SSIS vs t-sql datetime datatypes
Why there are such glaring differences and conflicts between t-sql data types and SSIS data types I just don’t understand. Here is one that may trip you up when casting a DateTime as int (yes I know casting a date as an int is silly, but there are legacy issues here).
SSIS cast as int works differently to t-sql cast as int due to different base dates as demonstrated:
Create four variables as below:
Set varIntFromDate to EvaluateAsEExpression = True, then enter the following expression
Note it evaluates to 40268
Set varIntFromDateTime to EvaluateAsEExpression = True, then enter the following expression
Note it evaluates to 40269
Problem: There appears to be some sort of rounding going on despite both variables having the same date (but different times).
Now do the same thing in t-sql:
DECLARE @varDate as datetime DECLARE @varDateTime as datetime SET @varDate = '20100331' SET @varDateTime = '20100331 18:50' SELECT @varDate as varDate ,@varDateTime as varDateTime ,cast(@varDate as int) as varIntFromDate ,cast(@varDateTime as int) as varIntFromDateTime
This time getting 40266 and 40267. I don’t know about you but last time I checked nothing from the list 40268, 40269, 40266, 40267 are the same.