Home » SQL & PL/SQL » SQL & PL/SQL » DateTime Query Problem (Oracle 10G, Windows Client using SQLPLUS)
DateTime Query Problem [message #360151] Wed, 19 November 2008 18:20 Go to next message
mivey4
Messages: 19
Registered: March 2007
Junior Member
I am having a problem transitioning the syntax from SQL to ORACLE for performing datetime queries although I am fairly confident that I've done this before in Oracle without this issue.

In SQL this works:

SELECT * FROM sometable
WHERE mydate BETWEEN '10/24/2008 6:05:00 PM' AND
'10/24/2008 7:05:00 PM'

However in Oracle I receive the error: ORA-01843 Not a valid month.

On this system the NLS_TIMESTAMP_FORMAT parameter states
DD-MON-RR HH.MI.SSXFF AM. But if I re-arrange the query as the format suggests:

SELECT * FROM sometable
WHERE mydate BETWEEN '24.10.2008 06.05.00 PM' AND
'24.10.2008 07.05.00 PM'

I still receive the same error. I know I can make this work by using the TO_DATE function like:
TO_DATE('10/24/2008','MM/DD/YYYY') but I actually need the specifics of time.

Should I create a custom procedure to make this work or is there an alternative method of using the original format of date and time to make this query work?

Any helpful assistance to point me in the right direction is appreciated.

thx Smile
Re: DateTime Query Problem [message #360152 is a reply to message #360151] Wed, 19 November 2008 18:32 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Hi, TO_DATE function can accept date/time parameters. Have fun with it. Smile

Try this:


select TO_DATE('10-23-04 17:15:05', 'MM-DD-YY HH24:MI:SS') as RESULT FROM DUAL;



Regards,
Wilbert

[Updated on: Wed, 19 November 2008 18:39]

Report message to a moderator

Re: DateTime Query Problem [message #360153 is a reply to message #360152] Wed, 19 November 2008 18:46 Go to previous message
mivey4
Messages: 19
Registered: March 2007
Junior Member
Hey thx wmgonzalbo.

I should have done more research on the TO_DATE function before posting this message. Confused I am use to coding in SQL and Oracle's syntax requires a bit more work.

After your post I found that I could in fact get exactly what I needed down to the meridian using:

SELECT * FROM someTable WHERE myDate BETWEEN to_date('10/16/2008 06:05.00 PM','MM/DD/YYYY HH:MI:SS AM') AND
TO_DATE('10/16/2008 07:05.00 PM','MM/DD/YYYY HH:MI:SS AM')

A bit more work than SQL but I can deal with it.

Thanks for the response.
Previous Topic: database trigger
Next Topic: Can't Create Index With An If Statement
Goto Forum:
  


Current Time: Fri Dec 09 15:31:12 CST 2016

Total time taken to generate the page: 0.13747 seconds