Change Excel date number to Oracle date
Solution 1
The value you have is the number of days since the 30th of December 1899. Try:
select to_char(
to_date('1899-12-30', 'YYYY-MM-DD') + 41293,
'DD/MON/YYYY') from dual
Solution 2
Quoting from Oracle forum:
You need a tool to do that, since format is to tell oracle what type of format you have on your date type in the spreadsheet. While you may not have opted to format the date in Excel, it will appear as a date in the previewer. Use the format from this as a guide to enter into the datatype panel.
so, if you have a date that looks like this in the previewer, 19-jan-2006, then your format for the data type panel if you choose to insert that column is going to be DD-MON-YYYY,
Option 1:
Try using the below functions
FUNCTION FROMEXCELDATETIME ( ACELLVALUE IN VARCHAR2 )
RETURN TIMESTAMP
IS
EXCEL_BASE_DATE_TIME CONSTANT TIMESTAMP
:= TO_TIMESTAMP ( '12/31/1899',
'mm/dd/yyyy' ) ;
VAL CONSTANT NUMBER
:= TO_NUMBER ( NULLIF ( TRIM ( ACELLVALUE ),
'0' ) ) ;
BEGIN
RETURN EXCEL_BASE_DATE_TIME
+ NUMTODSINTERVAL ( VAL
- CASE
WHEN VAL >= 60
THEN
1
ELSE
0
END,
'DAY' );
END;
FUNCTION TOEXCELDATETIME ( ATIMESTAMP IN TIMESTAMP )
RETURN VARCHAR2
IS
EXCEL_BASE_DATE_TIME CONSTANT TIMESTAMP
:= TO_TIMESTAMP ( '12/31/1899',
'mm/dd/yyyy' ) ;
DIF CONSTANT INTERVAL DAY ( 9 ) TO SECOND ( 9 )
:= ATIMESTAMP
- EXCEL_BASE_DATE_TIME ;
DAYS CONSTANT INTEGER := EXTRACT ( DAY FROM DIF );
BEGIN
RETURN CASE
WHEN DIF IS NULL
THEN
''
ELSE
TO_CHAR ( DAYS
+ CASE
WHEN DAYS >= 60
THEN
1
ELSE
0
END
+ ROUND ( ( EXTRACT ( HOUR FROM DIF )
+ ( EXTRACT ( MINUTE FROM DIF )
+ EXTRACT ( SECOND FROM DIF )
/ 60 )
/ 60 )
/ 24,
4 ) )
END;
END;
Option 2:
The excel function would be =TEXT(B2,"MM/DD/YY")
, to convert an Excel date value stored in B2. Then try using the test character in Oracle
If considering 1900 Jan 1st as start date,
SELECT
TO_CHAR ( TO_DATE ( '1900-01-01',
'YYYY-MM-DD' )
+ 41293,
'DD/MON/YYYY' )
FROM
DUAL
Related videos on Youtube
Sagar Tippe
Updated on October 10, 2022Comments
-
Sagar Tippe about 1 year
I'm having date as
41293
in oracle, how can i show it inDD/MON/YYYY
format?If i copy pasted it in Excel and change it to date format, it shows
01/19/13
Please help me. -
Sagar Tippe about 10 yearswhen i execute this SQL, the answer is 19/JAN/2013, but in data given the value is 21-JAN-13. Is the client data is wrong or this is because any other factors?
-
Sagar Tippe about 10 yearsthey are using this 41293 as a period_ID in their data warehouse. and the other attributes for that table are Calendar date:21-JAN-13, Day_Name: Monday, etc. please let me know the SQL which will convert 41293 to 21-JAN-13
-
Frank Schmitt about 10 yearsIn your question you wrote "it shows 01/19/13", which is returned by my SQL statement. But now you want the 21st of January ?
-
Sagar Tippe about 10 yearsi checked the client data just now. sorry for that, but this is what the data i got.. I think, the data i got from client is wrong.. Thanks Frank.
-
MT0 about 4 years"Note: Excel stores dates as sequential serial numbers so that they can be used in calculations. January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900." from Microsoft's documentation. Should this be
SELECT DATE '1899-12-31' + 41293 FROM DUAL
(as using the 30th December is one day out)? -
MT0 about 4 years"Note: Excel stores dates as sequential serial numbers so that they can be used in calculations. January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900." from Microsoft's documentation. Should this be
SELECT DATE '1899-12-31' + 41293 FROM DUAL
(as using the 1st January is one day out)? -
Frank Schmitt about 4 years@MT0 Interestingly, my version (using 1899-12-30) returns the wrong result for Jan 1st 1900, but the correct result for Jan 1st 2008 as opposed to the version using 1899-12-31 (I used the numbers from the link you provided:
select to_date('1899-12-30', 'YYYY-MM-DD') + 39448 from dual
It seems like somewhere between 1900 and 2008, either Oracle or Excel forgot one day or invented one :O -
MT0 about 4 years@FrankSchmitt Excel thinks that 60 is
1900-02-29
; however, 1900 was not a leap year so every day after that is 1 day out.