Convert from DateTime to INT

Question

In my SSIS package  I have to converting values from DateTime to a corresponding INTEGER value  The following sample has been provided   Any ideas as to how I can convert these   DATETIME   INT ---------  ---- 1 1 2009   39814 2 1 2009   39845 3 1 2009   39873 4 1 2009   39904 5 1 2009   39934 6 1 2009   39965 7 1 2009   39995 8 1 2009   40026 9 1 2009   40057 10 1 2009  40087 11 1 2009  40118 12 1 2009  40148 1 1 2010   40179 2 1 2010   40210 3 1 2010   40238 4 1 2010   40269 5 1 2010   40299 6 1 2010   40330

User · Answer

select DATEDIFF dd   12 30 1899   mydatefield

User · Answer

Or  once it s already in SSIS  you could create a derived column  as part of some data flow task  with    DT I8 FLOOR  DT R8 systemDateTime    But you d have to test to doublecheck

User · Answer

EDIT  Casting to a float int no longer works in recent versions of SQL Server  Use the following instead   select datediff day   1899-12-30T00 00 00   my date field  from mytable   Note the string date should be in an unambiguous date format so that it isn t affected by your server s regional settings     In older versions of SQL Server  you can convert from a DateTime to an Integer by casting to a float  then to an int   select cast cast my date field as float  as int  from mytable    NB  You can t cast straight to an int  as MSSQL rounds the value up if you re past mid day    If there s an offset in your data  you can obviously add or subtract this from the result  You can convert in the other direction  by casting straight back   select cast my integer date as datetime  from mytable

[sql-server] Convert from DateTime to INT

Examples related to sql-server

Examples related to ssis

Examples related to sql-server-2008-r2