In Oracle, convert number(5,10) to date

Question

When ececute the following SQL syntax in Oracle, always not success, please help.

40284.3878935185 represents '2010-04-16 09:18:34', with microsecond.

an epoch date of 01 January 1900 (like Excel).

create table temp1 (date1 number2(5,10));

insert into temp1(date1) values('40284.3878935185');

select to_date(date1, 'yyyy-mm-dd hh24:mi:ssxff') from temp1

Error report: SQL Error: ORA-01861: literal does not match format string 01861. 00000 - "literal does not match format string" *Cause: Literals in the input must be the same length as literals in the format string (with the exception of leading whitespace). If the "FX" modifier has been toggled on, the literal must match exactly, with no extra whitespace. *Action: Correct the format string to match the literal.

Thanks to Mark Bannister

Now the SQL syntax is:

select to_char(to_date('1899-12-30','yyyy-mm-dd') + 
date1,'yyyy-mm-dd hh24:mi:ss')  from temp1

but can't fetch the date format like 'yyyy-mm-dd hh24:mi:ss.ff'. Continue look for help.

Answer 1

Using an epoch date of 30 December 1899, try:

select to_date('1899-12-30','yyyy-mm-dd') + date1

Answer 2

Simple date addition doesn't work with timestamps, at least if you need to preserve the fractional seconds. When you do to_timestamp('1899-12-30','yyyy-mm-dd')+ date1 (in a comment on Mark's answer) the TIMESTAMP is implicitly converted to a DATE before the addition, to the overall answer is a DATE, and so doesn't have any fractional seconds; then you use to_char(..., '... .FF') it complains with ORA-01821.

You need to convert the number of days held by your date1 column into an interval. Fortunately Oracle provides a function to do exactly that, NUMTODSINTERVAL:

select to_timestamp('1899-12-30','YYYY-MM-DD')
    + numtodsinterval(date1, 'DAY') from temp3;

16-APR-10 09.18.33.999998400

You can then display that in your desired format, e.g. (using a CTE to provide your date1 value):

with temp3 as ( select 40284.3878935185 as date1 from dual)
select to_char(to_timestamp('1899-12-30','YYYY-MM-DD')
    + numtodsinterval(date1, 'DAY'), 'YYYY-MM-DD HH24:MI:SSXFF') from temp3;

2010-04-16 09:18:33.999998400

Or to restrict to thousandths of a second:

with temp3 as ( select 40284.3878935185 as date1 from dual)
select to_char(to_timestamp('1899-12-30','YYYY-MM-DD')+
    + numtodsinterval(date1, 'DAY'), 'YYYY-MM-DD HH24:MI:SS.FF3') from temp3;


2010-04-16 09:18:33.999

An epoch of 1899-12-30 sounds odd though, and doesn't correspond to Excel as you stated. It seems more likely that your expected result is wrong and it should be 2010-04-18, so I'd check your assumptions. Andrew also makes some good points, and you should be storing your value in the table in a TIMESTAMP column. If you receive data like this though, you still need something along these lines to convert it for storage at some point.

Answer 3

Don't know the epoch date exactly, but try something like:

select to_date('19700101','YYYYMMDD')+ :secs_since_epoch/86400 from dual;

Or, cast to timestamp like:

select cast(to_date('19700101', 'YYYYMMDD') + :secs_since_epoch/86400 as timestamp with local time zone) from dual;

Answer 4

I hope this doesn't come across too harshly, but you've got to totally rethink your approach here.

You're not keeping data types straight at all. Each line of your example misuses a data type.

• TEMP1.DATE1 is not a date or a varchar2, but a NUMBER
• you insert not the number 40284.3878935185, but the STRING >> '40284.3878935185' <<
• your SELECT TO_DATE(...) uses the NUMBER Temp1.Date1 value, but treats it as a VARCHAR2 using the format block

I'm about 95% certain that you think Oracle transfers this data using simple block data copies. "Since each Oracle date is stored as a number anyway, why not just insert that number into the table?" Well, because when you're defining a column as a NUMBER you're telling Oracle "this is not a date." Oracle therefore does not manage it as a date.

Each of these type conversions is calculated by Oracle based on your current session variables. If you were in France, where the '.' is a thousands separator rather than a radix, the INSERT would completely fail.

All of these conversions with strings are modified by the locale in which Oracle thinks your running. Check dictionary view V$NLS_PARAMETERS.

This gets worse with date/time values. Date/time values can go all over the map - mostly because of time zone. What time zone is your database server in? What time zone does it think you're running from? And if that doesn't spin your head quite enough, check out what happens if you change Oracle's default calendar from Gregorian to Thai Buddha.

I strongly suggest you get rid of the numbers ENTIRELY.

To create date or date time values, use strings with completely invariant and unambiguous formats. Then assign, compare and calculate date values exclusively, e.g.:

GOODFMT constant VARCHAR2 = 'YYYY-MM-DD HH24:MI:SS.FFF ZZZ'

Good_Time DATE = TO_DATE ('2012-02-17 08:07:55.000 EST', GOODFMT);