SQL Problem Urgent

[ba1234]

Hi.
Any one let me know why this is happening.

My SQL
Select to_date('01-SEP-2004','YYYY/MM/DD HH24:MI:SS') from dual

Output:
9/20/0001 4:00:00 AM

Its suppose to be 2004/09/01

We are using Oracle 8i 8.1.7

Thanks

[DaPi]

Look at what you coded. You said:
YYYY = 01
MM = SEP
DD HH24 = 2004

http://download-west.oracle.com/docs...a85397/toc.htm

I suspect you need to_char(to_date(.......)) - I'll leave the details for you to fill in: good learning excerise

[ba1234]

Hi dapi.

I already did that. but i need to pass as date not char.

[DaPi]

'01-SEP-2004' is a character string

Does this help your thought process?

Select to_char(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') from dual

TO_CHAR(SYSDATE,'YY
-------------------
2004/09/23 14:46:35

[ba1234]

Hi dapi.
No, I need to send date, not char.

Thanks

[slimdave]

Originally posted by ba1234
Hi.
Any one let me know why this is happening.

My SQL
Select to_date('01-SEP-2004','YYYY/MM/DD HH24:MI:SS') from dual

Output:
9/20/0001 4:00:00 AM

Its suppose to be 2004/09/01

We are using Oracle 8i 8.1.7

Thanks

Well the date format picture in the To_Date() function is supposed to match the format that you have used to write the date as in the string.

[DaPi]

If you want a DATE as output, why did you confuse the issue by posting two different char representations of it?

Originally posted by ba1234
Output:
9/20/0001 4:00:00 AM

Its suppose to be 2004/09/01

[mike9]

Hi,

As DaPi and Slimadave already said, the problem is that your date format is wrong.

You are using:
to_date('01-SEP-2004','YYYY/MM/DD HH24:MI:SS')

But it should be:
to_date('01-SEP-2004','DD-MON-YYYY')