Post reply

SSIS Expression help

  • July 23, 2020 at 4:43 am

    Go to Answer

    #3774040

    Hi Guys,/

    Is anyone can help me convert this SQL to SSIS Expression?

    SELECT DATEADD(DAY,DATEDIFF(DAY,0,CONVERT(DATE,DATEADD(WK,DATEDIFF(WK,0,GETDATE()),0) - 8)), '00:00:00')

    Thanks in Advance.

     

  • July 23, 2020 at 4:27 pm

    #3774223

    What have you tried so far?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • July 23, 2020 at 4:36 pm

    #3774224

    DATEADD("DD" ,DATEDIFF( "DD" , @[User::Todays_Date] ,CONVERT(DATE,DATEADD("WK",DATEDIFF( "WK", @[User::Todays_Date] ,GETDATE()), @[User::Todays_Date] ) - 28)), '00:00:00')

    @Todays_Date variable holds Today's date with DATETIME data type.

  • July 23, 2020 at 5:52 pm

    Answer

    #3774238

    I think the original SQL has some redundant parts in it, I believe it can be simplified to:

    SELECT DATEADD(DAY, -8, DATEADD(WK, DATEDIFF(WK, 0, GETDATE()), 0));

    Looking at the SSIS expression you tried, you are comparing the week of today vs week of today in your inner most DATEDIFF which will almost always be 0, so I believe that is a problem.  I'd try having a variable @starting which is assigned something like "01/01/1900" and using a formula like:

    DATEADD("DD", -8, DATEADD("WK",DATEDIFF( "WK", @[User::starting], GETDATE()), @[User::starting] ) )

  • July 24, 2020 at 1:22 am

    #3774301

    Just tested Chris' solution and it seems to do the job.

    2020-07-23_20-20-41

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • July 24, 2020 at 4:36 am

    #3774315

    Thanks, Chris. I appreciate your help.

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

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