1

I'm reading a column that's a short date that I need to convert to a long date or a string. Excel can do this readily , but I can't find out how to do it with Oracles SQL

Examples of the DATETIME column:

41590.6753233101
41593.7843996875
41593.7844002199
41593.7844007638

I would like to convert this to a human readable date. Hate to have to direct someone move the out put to excel if you want to see the date.

Mark Stewart
  • 2,046
  • 4
  • 22
  • 32
user2117002
  • 27
  • 8

1 Answers1

3

Excel stores dates as the number of days since January 1, 1900, so to convert an Excel date, you just add them.

with mydates as (select 41590.6753233101 as datetime from dual
    union select 41593.7843996875 from dual
    union select 41593.7844002199 from dual
    union select 41593.7844007638 from dual)
select datetime, DATE '1899-12-30' + datetime
from mydates;

Output:

41590.6753233101    11/12/2013 4:12:28 PM
41593.7843996875    11/15/2013 6:49:32 PM
41593.7844002199    11/15/2013 6:49:32 PM
41593.7844007638    11/15/2013 6:49:32 PM
kfinity
  • 8,581
  • 1
  • 13
  • 20
  • 2
    I think we must add `'1899-12-30'` because Microsoft incorrectly assumes that 1990 is a Leap Year : see https://stackoverflow.com/questions/13850605/convert-excel-date-serial-number-to-regular-date and https://support.microsoft.com/en-us/help/214326/excel-incorrectly-assumes-that-the-year-1900-is-a-leap-year – Kaushik Nayak Oct 24 '18 at 16:10
  • Note that @kfinity has done a `alter session set nls_date_format = 'mm/dd/yyyy hh:mi:ss';` to get that format of day/time. – Mark Stewart Oct 24 '18 at 16:21