Convert from DateTime to INT

Question

In my SSIS package, I need to convert the values ​​from DateTime to the corresponding INTEGER value. The following sample has been provided.

Any ideas on how I can convert them?

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 

Answer 1

EDIT: Casting to 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 

Please note that the string date must be in an explicit date format so as not to affect the regional settings of your server.

---

In older versions of SQL Server, you can convert from DateTime to Integer by casting to float and then to int:

 select cast(cast(my_date_field as float) as int) from mytable 

(NB: you cannot use directly in int since MSSQL rounds the value up if you are in the middle of the day!)

If there is an offset in your data, you can obviously add or subtract this from the result

You can convert in the other direction, swinging straight back:

 select cast(my_integer_date as datetime) from mytable 

Answer 2

select DATEDIFF (dd, '12 / 30/1899 ', mydatefield)

Answer 3

Or, once it's already in SSIS, you can create a derived column (as part of some data flow task) with

 (DT_I8)FLOOR((DT_R8)systemDateTime) 

But you will need to check double check.