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

Answer 1

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

Answer 2

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