SSIS Base Date != T-SQL Base Date!

Couple of days before I was replicating out a logic in SSIS from T-SQL and I was in for a surprise. I’m sharing it through this blog post for others benefit.
I’d blogged sometime back on how to develop server independent date logic in T-SQL queries

http://visakhm.blogspot.in/2012/08/creating-server-independent-day.html

I had a stored procedure where I was using the above logic to determine current day value and do some manipulations based on that. I tried to replicate the same logic inside SSIS and wrote similar expression inside expression builder as below

And here comes the surprise factor!

This was not working as expected. I rechecked the logic in T-SQL and was able to get it correctly.This had me confused for a while.

My next attempt was to find out the reason for this difference of behavior. I just tried getting the base date out from SSIS. So I changed expression as per below and look at what I got as result.

As you see it returns 30th Dec 1899 as the base date which is different from SQL Server base date (1st jan 1900). This was the reason why our calculation went wrong as 30th Dec 1899 is Saturday which will always push our calculation by 2 days behind compared to T-SQL.
So for anyone who relies upon similar logic, keep in mind the below points while implementing this in SSIS

1. Base date in SSIS is 30 Dec 1899 and not 1 Jan 1900
2. To implement logic, either add 2 days to T-SQL logic or explicitly pass 1 Jan 1900 as start date instead of integer value 0 in SSIS to get the same behavior as in T-SQL
.
ie Either of the below

DATEDIFF(“day”,(DT_DBDATE)2,GETDATE())

DATEDIFF(“day”, (DT_DBTIMESTAMP)”1900-01-01″,GETDATE())

Published by dataplatformcentral