Home » SQL & PL/SQL » SQL & PL/SQL » sysdate - sql query (10g)
sysdate - sql query [message #418396] Fri, 14 August 2009 10:50 Go to next message
joe345
Messages: 4
Registered: August 2009
Junior Member
I put a record into a table using sysdate and using oracle developer it displays it as 09-08-14 with no time. I wanna compare 2 dates put i get an error that says: literal does not match format string. See anything wrong?

my code:

select count(userid)
from downloadfilestats
where dldate >= to_date('09-08-01')
and dldate <= to_date('09-08-31')

Re: sysdate - sql query [message #418398 is a reply to message #418396] Fri, 14 August 2009 10:57 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
The fact that oracle developer doesn't display a time doesn't mean it doesn't have one. All oracle dates have a time component, but if you don't specify a value it'll be midnight.

Since you used sysdate it will have the time as when you ran the insert.

Read up on nls_date_format.

And when using to_date always use a format mask, and 4 digit years to avoid any ambiguity.

EDIT: typos

[Updated on: Fri, 14 August 2009 10:59]

Report message to a moderator

Re: sysdate - sql query [message #418399 is a reply to message #418398] Fri, 14 August 2009 11:12 Go to previous messageGo to next message
joe345
Messages: 4
Registered: August 2009
Junior Member
I did this. to_date(2009-08-14,'YYYYMMDD') but i get ORA-01843: not a valid month
Re: sysdate - sql query [message #418400 is a reply to message #418396] Fri, 14 August 2009 11:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I did this. to_date(2009-08-14,'YYYYMMDD') but i get ORA-01843: not a valid month
you did it wrong.
Two problems
1) first argument is a string & requires single quote marks
2) mask does not match first argument (dashes vs. no dashes)
Re: sysdate - sql query [message #418466 is a reply to message #418396] Sat, 15 August 2009 18:42 Go to previous messageGo to next message
hadweir
Messages: 8
Registered: August 2009
Location: Manchester United
Junior Member
I think something is wrong with that code; try this, see if it works.

select count(userid)
from downloadfilestats
where dldate >= to_date(09-08-01,'fmDD-MM-YY')
and dldate <= to_date(09-08-31,'DD-MM-RR')
Re: sysdate - sql query [message #418469 is a reply to message #418396] Sat, 15 August 2009 20:54 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
hahadweir,
>where dldate >= to_date(09-08-01,'fmDD-MM-YY')
don't strings need single quote marks? '09-08-01'
>and dldate <= to_date(09-08-31,'DD-MM-RR')
Which year, 1931 or 2031 & why?

You are zero for two tonight.
Perhaps you should quit while you are behind.

[Updated on: Sat, 15 August 2009 21:02]

Report message to a moderator

Previous Topic: date question
Next Topic: Comparision Usage of DBLINK & MATERIALIZE VIEW
Goto Forum:
  


Current Time: Tue Feb 11 11:30:22 CST 2025