Home » SQL & PL/SQL » SQL & PL/SQL » sql query
sql query [message #417836] Tue, 11 August 2009 13:29 Go to next message
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
Re: sql query [message #417838 is a reply to message #417836] Tue, 11 August 2009 13:40 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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
Re: sql query [message #417839 is a reply to message #417838] Tue, 11 August 2009 13:43 Go to previous messageGo to next message
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?
Re: sql query [message #417840 is a reply to message #417836] Tue, 11 August 2009 13:48 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>where dldate = to_char(sysdate,'yy/mm/dd')
where TRUNC(dldate) = TRUNC(sysdate)
Re: sql query [message #417841 is a reply to message #417836] Tue, 11 August 2009 13:50 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: No rows selected but data exists
Next Topic: A tricky case in EXISTS
Goto Forum:
  


Current Time: Sat Dec 10 09:24:20 CST 2016

Total time taken to generate the page: 0.08585 seconds