RE: SQL question from an absolute SQL newbie

The only problem with this, as I just recently found out, is that if the numeric field representing the date (in this case post_date) is NOT a true date, then
Date(digits(post_date) will fail (see my prior e-mails)
The way that I circumvented this problem is in fact by installing iDate as suggested by numerous responses
I have just recently installed iDate on all our systems, and by using iDate, my problem is no more

Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Schutte, Michael D
Sent: Wednesday, August 31, 2011 8:06 AM
To: Midrange Systems Technical Discussion
Subject: RE: SQL question from an absolute SQL newbie

Instead of using iDate, use Date.

Where Date(digits(post_date) concat '000000') = current_date - 1 days


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Tom Hightower
Sent: Tuesday, August 30, 2011 7:34 PM
To: Midrange Systems Technical Discussion
Subject: SQL question from an absolute SQL newbie

Once again I come to the masters of all things SQL...

I have 2 files:

File TCKTHIST:
TICKET#
CUSTOMER#
TRAN_DATE (CCYYMMDD, likely not the same value as POST_DATE) POST_DATE (CCYYMMDD)


File NEWTCKTS:
TICKET#
CUSTOMER#
TRAN_DATE (CCYYMMDD)

I need to work up an SQL statement that will find all entries in TCKTHIST with a post_date = yesterday and which are not in file NEWTCKTS. Fields TICKET#, CUSTOMER# and TRAN_DATE are common fields to both files; POST_DATE is only in TCKTHIST.





________________________________

Notice from Bob Evans Farms, Inc: This e-mail message, including any attachments, may contain confidential information that is intended only for the person or entity to which it is addressed. Any unauthorized review, use, disclosure or distribution is strictly prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message and any attachments.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.