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
This question is related to
sql-server
ssis
sql-server-2008-r2
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
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.
select DATEDIFF(dd, '12/30/1899', mydatefield)
Source: Stackoverflow.com