How to Convert Excel Date Serial Numbers to Regular Dates?

Converting Excel Date Serial Numbers to Regular Dates

In data processing, it is often necessary to manipulate dates stored as Excel Date Serial Numbers. These numbers represent the number of days elapsed since December 30, 1899.

To convert these serial numbers to regular dates, you can use the following methods:

SQL:

• DATEADD Function: Add the serial number to the origin date (December 30, 1899) using the DATEADD function:

select dateadd(d, 36464, '1899-12-30')

• CAST Function: Alternatively, you can use the CAST function to convert the serial number to a SmallDateTime data type:

select CAST(36464 - 2 as SmallDateTime)

SSIS:

• Derived Column Transformation: In SQL Server Integration Services (SSIS), you can use the Derived Column Transformation to convert the serial numbers using the following expression:

CAST((DT_DBDATE)([DateOfBirth] - 2) AS DATETIME)

This expression subtracts 2 from the serial number and converts the result to a DATETIME value.

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