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:

t-sql has a base date of 1900-01-01 00:00:00 while SSIS has a base date of 1899-12-30

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'

  @varDate as varDate    
 ,@varDateTime  as varDateTime     
 ,cast(@varDate as int)  as varIntFromDate    
 ,cast(@varDateTime as int)   as varIntFromDateTime


to get the following results:

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.


About Karl Beran

I take interest and satisfaction from enabling people within an organisation to have an accurate, concise and flexible representation of the right information they need, when (or before) they need it. I believe this allows the business to make both operational and strategic decisions quickly and with more confidence, thus improving the agility and efficiency of the company. Having worked with BI stacks from the major vendors, the focus of my blog is on BI principals and strategy with examples from the Microsoft stack as I believe it has the most dynamic and engaged user community with the knowledge base and components fairly readily available to new developers.

Posted on March 31, 2010, in SSIS. Bookmark the permalink. 2 Comments.

  1. I’ve noticed Marco Russo (blog | twitter) has written a relevant post about Date behaviour in PowerPivot.


  1. Pingback: Power Query: Week or Weak | Karl Beran's BI mumble

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: