How Do I Convert Excel Date Serial Numbers to Regular Dates in SQL and SSIS?

Converting Excel Date Serial Numbers to Regular Dates

Date serial numbers are often encountered in Excel files, and it becomes necessary to convert them to regular dates for further processing or analysis.

To convert Excel date serial numbers in SQL, use the following syntax:

SELECT CAST(36464 - 2 AS SmallDateTime)

This calculation subtracts 2 from the serial number and casts the result to the SmallDateTime data type. The constant 2 is used because Excel considers January 1, 1900 as day 1, while SQL Server considers December 30, 1899 as day 0.

For example:

SELECT CAST(36464 - 2 AS SmallDateTime)
-- Output: 1999-01-11

In SSIS, the "Date Time" data type supports Excel date serial numbers. To convert them, use the "Convert Date Time" transformation, selecting the "Excel Julian" option for the "Time Representation" property.

The above is the detailed content of How Do I Convert Excel Date Serial Numbers to Regular Dates in SQL and SSIS?. For more information, please follow other related articles on the PHP Chinese website!