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
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())