OraFAQ Forum: Server Administration » Help! Where clause using TO

Home » RDBMS Server » Server Administration » Help! Where clause using TO_DATE

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Help! Where clause using TO_DATE [message #374092]

Thu, 24 May 2001 10:54

Mhoward
Messages: 4
Registered: May 2001

Junior Member

I am need of your help. I m using Oracle 8.05 and have a fairly simple query that returns a count of records added on the current date. (adddate field)

So far, the only way I can get it to work is using a date specific clause of:
WHERE adddate >= TO_DATE('24-may-2000','dd-mon-yyyy')

I want to use SYSDATE so that I can use the statement in ASP pages and not have to manually find/replace the hard coded date. However, when I use sysdate in place of the actual date in the TO_DATE conversion, I get different record counts. Why? What should I use to product the same results as hard coding the specific date? I have been working on this one for some time without any success.

Here is the sysdate clause I have attempted to use:
WHERE adddate >= TO_DATE(sysdate,'dd-mon-yyyy')

Any ideas?

Thanks,

Mitch

Report message to a moderator

Re: Help! Where clause using TO_DATE [message #374093 is a reply to message #374092]

Thu, 24 May 2001 10:58

sverch
Messages: 582
Registered: December 2000

Senior Member

What is in your addate?
I mean is it just a SYSDATE? Or TO_DATE(sysdate,'dd-mon-yyyy')?

Report message to a moderator

Re: Help! Where clause using TO_DATE [message #374094 is a reply to message #374092]

Thu, 24 May 2001 11:17

Mhoward
Messages: 4
Registered: May 2001

Junior Member

The field has a datatype of Date. I believe that the application stores the sysdate in this field when the record is added. It is not translated before it is stored.

Thanks,

Mitch

Report message to a moderator

Re: Help! Where clause using TO_DATE [message #374095 is a reply to message #374092]

Thu, 24 May 2001 11:19

Darran
Messages: 2
Registered: May 2001

Junior Member

You shouldn't been using TO_DATE with SYSDATE.
Just use TRUNC to get the date part from SYSDATE (ie removing the time element)

WHERE adddate >= TRUNC(sysdate)

Report message to a moderator

Re: Help! Where clause using TO_DATE [message #374096 is a reply to message #374092]

Thu, 24 May 2001 11:21

Darran
Messages: 2
Registered: May 2001

Junior Member

You shouldn't been using TO_DATE with SYSDATE.
Just use TRUNC to get the date part from SYSDATE (ie removing the time element)

WHERE adddate >= TRUNC(sysdate)

Report message to a moderator

Re: Help! Where clause using TO_DATE [message #374097 is a reply to message #374092]

Thu, 24 May 2001 11:23

Mhoward
Messages: 4
Registered: May 2001

Junior Member

The field has a datatype of Date. I believe that the application stores the sysdate in this field when the record is added. It is not translated before it is stored.

Thanks,

Mitch

Report message to a moderator