InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 18:21:30 GMT20http://www.sqlservercentral.com/Forums/Topic1391955-364-1.aspxAs Ray indicated earlier, (DT_DBDATE)0 evaluates to 1899-12-30. This would be equivalent to using -2 inplace of 0 in the T-SQL calls as shown here:[code="sql"]/*Date Available in db Result Expected Result01/11/2012 01:28 03/11/2012 00:00 29/10/2012 00:0004/11/2012 19:12 10/11/2012 00:00 03/11/2012 00:0006/11/2012 09:34 10/11/2012 00:00 03/11/2012 00:0012/11/2012 10:59 17/11/2012 00:00 10/11/2012 00:00*/WITH TestData AS (SELECT cast(TestDate as datetime) TestDateFROM (VALUES ('20121101 01:28'),('20121104 19:12'),('20121106 09:34'),('20121112 10:59'))dt(TestDate))select TestDate, dateadd(wk,datediff(wk,0,TestDate),0), dateadd(wk,datediff(wk,-2,TestDate),-2)from TestData;[/code]Mon, 03 Dec 2012 21:10:08 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1391955-364-1.aspx[quote][b]var05 (12/3/2012)[/b][hr]Hi All,I wanted to find the starting of the week and poulate it as an additional column. Im trying to use dervived column in SSISSQL Equivalent is:SELECT DATEADD(WK, DATEDIFF(WK,0,'11/01/2012'), 0)2012-10-29 00:00:00.000 - FineIm not able to use the same sql statement in SSIS dervived col. I tried using the below expression:DATEADD("WK",DATEDIFF("WK",(DT_DBDATE)0,'11/01/2012'),(DT_DBDATE)0).... --- But this returns diff results 03/11/2012 00:00:00. ---It returns the subsequent saturday and not the staring day of the week i.e Monday. Any help on this?Thanks![/quote]Change this:DATEADD("WK",DATEDIFF("WK",(DT_DBDATE)0,'11/01/2012'),(DT_DBDATE)0).... To this:DATEADD("WK",DATEDIFF("WK",(DT_DBDATE)"1900-01-01",'11/01/2012'),(DT_DBDATE)"1900-01-01")....Let us know if this works.Mon, 03 Dec 2012 09:33:01 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1391955-364-1.aspxThanks for your reply..I tired using all the datetime expressions related to date with the below exprseeion in dervived columnDATEADD("WK",DATEDIFF("WK",0,date),0)- but the same query gives correct answers in sql , but shows error in SSIS dervived expressions.I get results this way :-Date Available in db Result Expected Result01/11/2012 01:28 03/11/2012 00:00 29/10/2012 00:0004/11/2012 19:12 10/11/2012 00:00 03/11/2012 00:0006/11/2012 09:34 10/11/2012 00:00 03/11/2012 00:0012/11/2012 10:59 17/11/2012 00:00 10/11/2012 00:00How to achieve the expected results using dervived column in SSIS- every date available in DB, should show the starting day - Monday of that particular week.Thanks!Expected result-(29/10,03/11,10/11 are the starting of the week-Monday)Mon, 03 Dec 2012 09:09:35 GMTvar05http://www.sqlservercentral.com/Forums/Topic1391955-364-1.aspx[code="sql"]SELECT CAST(0 AS DATETIME)[/code]Resolves to [quote]1900-01-01 00:00:00.000[/quote][code="plain"](DT_DBDATE)0[/code]Resolves to [quote]1899-12-30[/quote]There's your two days differenceMon, 03 Dec 2012 08:55:19 GMTRay Mhttp://www.sqlservercentral.com/Forums/Topic1391955-364-1.aspxHi All,I wanted to find the starting of the week and poulate it as an additional column. Im trying to use dervived column in SSISSQL Equivalent is:SELECT DATEADD(WK, DATEDIFF(WK,0,'11/01/2012'), 0)2012-10-29 00:00:00.000 - FineIm not able to use the same sql statement in SSIS dervived col. I tried using the below expression:DATEADD("WK",DATEDIFF("WK",(DT_DBDATE)0,'11/01/2012'),(DT_DBDATE)0).... --- But this returns diff results 03/11/2012 00:00:00. ---It returns the subsequent saturday and not the staring day of the week i.e Monday. Any help on this?Thanks!Mon, 03 Dec 2012 07:48:52 GMTvar05