sql - convert Excel Date Serial Number to Regular Date

Question

I got a column called DateOfBirth in my csv file with Excel Date Serial Number Date

Example:

  36464
  37104
  35412

When i formatted cells in excel these are converted as

  36464 => 1/11/1999
  37104 => 1/08/2001
  35412 => 13/12/1996

I need to do this transformation in SSIS or in SQL. How can this be achieved?

Answer 1

In SQL:

select dateadd(d,36464,'1899-12-30')
-- or thanks to rcdmk
select CAST(36464 - 2 as SmallDateTime)

In SSIS, see here

http://msdn.microsoft.com/en-us/library/ms141719.aspx