SSIS datediff and dateadd

• var05

  Hall of Fame

  Points: 3011

  More actions

  December 3, 2012 at 7:48 am

  #267681

  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 SSIS

  SQL Equivalent is:

  SELECT DATEADD(WK, DATEDIFF(WK,0,'11/01/2012'), 0)

  2012-10-29 00:00:00.000 - Fine

  Im 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!

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  December 3, 2012 at 8:55 am

  #1564932

  SELECT CAST(0 AS DATETIME)

  Resolves to

  1900-01-01 00:00:00.000

  (DT_DBDATE)0

  Resolves to

  1899-12-30

  There's your two days difference

• var05

  Hall of Fame

  Points: 3011

  More actions

  December 3, 2012 at 9:09 am

  #1564939

  Thanks for your reply..

  I tired using all the datetime expressions related to date with the below exprseeion in dervived column

  DATEADD("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 Result

  01/11/2012 01:2803/11/2012 00:0029/10/2012 00:00

  04/11/2012 19:1210/11/2012 00:0003/11/2012 00:00

  06/11/2012 09:3410/11/2012 00:0003/11/2012 00:00

  12/11/2012 10:5917/11/2012 00:0010/11/2012 00:00

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

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  December 3, 2012 at 9:33 am

  #1564948

  var05 (12/3/2012)

  ---

  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 SSIS

  SQL Equivalent is:

  SELECT DATEADD(WK, DATEDIFF(WK,0,'11/01/2012'), 0)

  2012-10-29 00:00:00.000 - Fine

  Im 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!

  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.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  December 3, 2012 at 9:10 pm

  #1565120

  As 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:

  /*

  Date Available in db Result Expected Result

  01/11/2012 01:28 03/11/2012 00:00 29/10/2012 00:00

  04/11/2012 19:12 10/11/2012 00:00 03/11/2012 00:00

  06/11/2012 09:34 10/11/2012 00:00 03/11/2012 00:00

  12/11/2012 10:59 17/11/2012 00:00 10/11/2012 00:00

  */

  WITH TestData AS (

  SELECT

  cast(TestDate as datetime) TestDate

  FROM

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