OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » sql query

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

sql query [message #417836]

Tue, 11 August 2009 13:29

joe345
Messages: 4
Registered: August 2009

Junior Member

Im trying to get all the records for the current date (todays)

my column field is set as date and a record is shown as

id filepath dldate userid
4 df//fdfdfd 09-08-11 pjoe

my query is

select count(userid) as downloadstoday
from downloadfilestats
where dldate = to_char(sysdate,'yy/mm/dd')

but im getting this error

Error Executing Database Query.
[Macromedia][Oracle JDBC Driver][Oracle]ORA-01843: not a valid month

Does anyone know whats wrong with the query?
thx

Report message to a moderator

Re: sql query [message #417838 is a reply to message #417836]

Tue, 11 August 2009 13:40

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Does anyone know whats wrong with the query?
DATE data type also contains a time component.
You should never rely upon implicit data type conversion between DATE & characters (strings, TO_CHAR()) because this is the cause of the error

Report message to a moderator

Re: sql query [message #417839 is a reply to message #417838]

Tue, 11 August 2009 13:43

joe345
Messages: 4
Registered: August 2009

Junior Member

how do i solve this? can i set the type to something other that just has the date?

Report message to a moderator

Re: sql query [message #417840 is a reply to message #417836]

Tue, 11 August 2009 13:48

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>where dldate = to_char(sysdate,'yy/mm/dd')
where TRUNC(dldate) = TRUNC(sysdate)

Report message to a moderator

Re: sql query [message #417841 is a reply to message #417836]

Tue, 11 August 2009 13:50

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

Report message to a moderator

Previous Topic:	No rows selected but data exists
Next Topic:	A tricky case in EXISTS

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jul 03 20:30:14 CDT 2025