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

Excel Date Serial Number Conversion to Regular Date in SQL and SSIS

Data files often contain date information in Excel Date Serial Number format. This format represents dates as a sequential number of days since December 30, 1899. While Excel can easily convert these numbers into regular dates, it's crucial to know how to perform this conversion in programming languages such as SQL or SSIS.

SQL Solution

In SQL, you can use the following formula to convert an Excel Date Serial Number to a regular date:

SELECT DATADD(D, [DateOfBirth], '1899-12-30');

Alternatively, another method suggested by a contributor is:

SELECT CAST([DateOfBirth] - 2 AS SmallDateTime);

SSIS Solution

In SSIS, you can accomplish this conversion using the Date and Time Conversion component. The following steps outline the process:

1. Drag a Date and Time Conversion component onto the SSIS canvas.
2. Double-click the component to open the Transformation Editor window.
3. Select the "TIBCO Date Serial Number" option from the "Source DataType" dropdown list.
4. Select the desired output date format from the "Destination DataType" dropdown list.
5. Connect the input and output columns to the component.

Example Input and Output

Consider the following input Excel Date Serial Numbers:

  36464
  37104
  35412

The corresponding output regular dates in MM/dd/yyyy format would be:

  01/11/1999
  01/08/2001
  13/12/1996

The above is the detailed content of How to 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!