Menu
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?
943
Add an Apostrophe before the Number If you only have to enter a number in a few cells and you don't want Excel to change it to date, you can use this simple technique. Just add an apostrophe sign before you enter the number (as shown below).
Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, 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. Most functions automatically convert date values to serial numbers.
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
170
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
