RE: Touble With Dates

2006-05-26 Thread Ian Harisay
TECTED] Sent: Thursday, May 25, 2006 1:40 PM To: Joseph Lamb Cc: dbi-users@perl.org Subject: Re: Touble With Dates Cliff Nadler wrote: > Try the following instead: > > trunc(SHIPDATE) = trunc(SYSDATE)-21 > > Oracle's date fields (and especially SYSDATE) are date + time. The

RE: Touble With Dates

2006-05-25 Thread Warden, Ronald MCF:EX
h Lamb Cc: dbi-users@perl.org Subject: RE: Touble With Dates But who would want to have a query only match a down-to-the-second timestamp exactly 21 days ago from the time of the query? If the intent of the query is to find all entries made anytime during the day 21 days ago, you wi

Re: Touble With Dates

2006-05-25 Thread Joseph Lamb
That trick worked. Thank you On 5/25/06, Cliff Nadler <[EMAIL PROTECTED]> wrote: Try the following instead: trunc(SHIPDATE) = trunc(SYSDATE)-21 Oracle's date fields (and especially SYSDATE) are date + time. The TRUNC will trucate the value to the date only (actually midnight) so they can c

Re: Touble With Dates

2006-05-25 Thread mark
Warden, Ronald MCF:EX wrote: An easier way to deal with this is to set the NLS_DATE_FORMAT at the beginning of your program. This requires you to alter your session and set the NLS_DATE_FORMAT. After that all your date will be displayed in whatever format you choose. Note that the original post

Re: Touble With Dates

2006-05-25 Thread Dr.Ruud
"Joseph Lamb" schreef: > s.shipdate > > TO_CHAR(SHIPDATE, 'MMDDYY') = TO_CHAR(SYSDATE-21, 'MMDDYY') and Maybe you need to replace "SHIPDATE" by "s.shipdate"? -- Affijn, Ruud "Gewoon is een tijger."

Re: Touble With Dates

2006-05-25 Thread mark
Hedstrom, Charlie wrote: If the intent of the query is to find all entries made anytime during the day 21 days ago, you will need the trunk function. Nope. SQL> select * from x_ship where to_char(shipdate, 'MMDDYY') = to_char(sysdate-21, 'MMDDYY') SHIPDATE --- 2006-05-04

RE: Touble With Dates

2006-05-25 Thread Hedstrom, Charlie
: mark [mailto:[EMAIL PROTECTED] Sent: Thursday, May 25, 2006 3:40 PM To: Joseph Lamb Cc: dbi-users@perl.org Subject: Re: Touble With Dates Cliff Nadler wrote: > Try the following instead: > > trunc(SHIPDATE) = trunc(SYSDATE)-21 > > Oracle's date fields (and especially SYSDAT

Re: Touble With Dates

2006-05-25 Thread mark
Cliff Nadler wrote: Try the following instead: trunc(SHIPDATE) = trunc(SYSDATE)-21 Oracle's date fields (and especially SYSDATE) are date + time. The TRUNC will trucate the value to the date only (actually midnight) so they can compare correctly. It would be curious if trunc works, but ther

Re: Touble With Dates

2006-05-25 Thread Cliff Nadler
Try the following instead: trunc(SHIPDATE) = trunc(SYSDATE)-21 Oracle's date fields (and especially SYSDATE) are date + time. The TRUNC will trucate the value to the date only (actually midnight) so they can compare correctly. On Thu, 2006-05-25 at 14:06 -0400, Joseph Lamb wrote: > The program

Touble With Dates

2006-05-25 Thread Joseph Lamb
The program below prints nothing when the date filter is in the where clause (TO_CHAR(SHIPDATE, 'MMDDYY') = TO_CHAR(SYSDATE-21, 'MMDDYY') ). The query work fine when I take this statement out. The query also works fine when I run it form the CLI. What do I need to do to make this program work? Y