SSIS datediff and dateadd

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

  • 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

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

  • 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.

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply