SSIS Expression help

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

     

  • What have you tried so far?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

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

  • 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] ) )
  • Just tested Chris' solution and it seems to do the job.

    2020-07-23_20-20-41

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Thanks, Chris. I appreciate your help.

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

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