OraFAQ Forum: SQL & PL/SQL

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

Show: Today's Messages :: Show 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: 25542
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: 25542
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: 65088
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

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: Wed Jul 26 18:09:41 CDT 2017

Total time taken to generate the page: 0.11592 seconds