oracle - to_Date format

Question

What format would you use to convert this CHAR value into a date?

Apr  9 1996  2:11:00:000PM


ie. select to_date('Apr  9 1996  2:11:00:000PM','???') from dual;

Has anyone found a good reference because all the ones I see expect that the hour has a preceding 0 to start with.

Please refer to this http://stackoverflow.com/q/1758219/1268469 — Deb, Apr 12 '12 at 07:31

score 4 · Accepted Answer · answered Apr 12 '12 at 07:50

4

Here is the sample with your date string.

select to_date(
to_char(to_timestamp('Apr 9 1996 2:11:00:000PM','MON DD YYYY hh:mi:ss:FF3PM'),'YYYY-MM-DD HH:MI:SS AM'),
'YYYY-MM-DD HH:MI:SS AM')
from dual;

Hope it works.

answered Apr 12 '12 at 07:50

Deb

981
13
39

BigMike · Answer 2 · 2012-04-12T07:20:06.637

2

First: TO_DATE doesn't support fractional seconds, so you have to play around with TO_TIMESTAMP

Second: you have to use oracle's formats you can find a table of valid values here

At first glance a format like 'MON DD YYYY HH:MI:SS:FF3 AM' should work

Edit: too low on coffeine today, forgot the AM ^^ Sorry

edited Apr 12 '12 at 07:20

answered Apr 12 '12 at 07:14

BigMike

6,683
1
23
24

score 1 · Answer 3 · answered Apr 12 '12 at 07:16

1

This might work (untested):

'Mon  dd YYYY   HH:MI:SS   AM'

answered Apr 12 '12 at 07:16

wallyk

56,922
16
83
148

oracle - to_Date format

3 Answers3