How to format Date using to_date

[ksbigfoot]

My date is in this format: 2008-01-17 5:23:20 PM
I am using: to_date('2008-01-17 5:23:20 PM', 'yyyy-mm-dd hh24:mi:ss'), but this gives me an error.
Do you know how the to_date should look?

 

[BJCooperIT]

Your literal is:

'2008-01-17 5:23:20 PM'

which needs a format of:

'yyyy-mm-dd hh:mi:ss am'

Are your hours really hh24? Normally a date that has been to_char'ed is either hh24 or AM/PM, but not both.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[ksbigfoot]

Howdy BJCooperIT,

I put to_date('2008-01-17 5:23:20 PM', 'yyyy-mm-dd hh:mi:ss am')
and I get the error message: "Error while trying to retrieve text for error ORA-00972

 

[ksbigfoot]

Forgot to mention that my date field in my Oracle database is in the format 'yyyy-mm-dd hh24:mi:ss'

 

[BJCooperIT]

Could you post a describe of your table and some sample data?

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[anto2]

have you tried this in sqlplus

select to_char(to_date('2008-01-17 5:23:20 PM', 'yyyy-mm-dd hh:mi:ss am'),'yyyy-mm-dd hh24:mi:ss') from dual;

this returned 2008-01-17 17:23:20 for me.

As BJCooperIT pointed out having hh24 and am/pm is unusual and in sqlplus trying this gives an error.

select to_date('2008-01-17 5:23:20 PM', 'yyyy-mm-dd hh24:mi:ss am') from dual;

ORA-01818: 'HH24' precludes use of meridian indicator

 

[Andrzejek]

date field in my Oracle database is in the format 'yyyy-mm-dd hh24:mi:ss'

Actually, your date field in ORCALE is NOT in any format. If the field is a DATE, it holds a number, something like 1234567.987654, and depending on how you want to see it, you (or a tool you use to display this data) formats the info for you.

The part of 1234567 is how many days since (I guess) Jan 1, 1900, and the part of .987654 is the time since the midnight of that day.


Have fun.

---- Andy

 

[sem]

Andrzejek , your answer is only partly correct. Date field is indeed stored without format information, though its internal structure is completely different:

http://www.ixora.com.au/notes/date_representation.htm

Regards, Dima

http://seminihin.narod.ru