Post reply

Calculate last friday date with timestamp ssis

  • June 13, 2019 at 10:11 pm

    #3653557

    hello,

    i want to calculate last friday’s date in ssis with timestamp. Below gives me only date. Can u help?

    DATEADD("dd", -1 - (DATEPART("dw", getdate()) % 7), getdate())

     

    Also 2) i want to find last friday until 3 pm.

    How to do that?? Thanks a lot

  • June 13, 2019 at 10:30 pm

    #3653563

    Your expression returns a datetime, as can be seen below:

    2019-06-13_17-28-04

    "Also 2) i want to find last friday until 3 pm." This makes no sense to me, can you clarify, please? Ideally with two or three examples.

    This date is also not Friday!

    • This reply was modified 5 years, 6 months ago by  Phil Parkin. Reason: Additional text

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • June 13, 2019 at 10:36 pm

    #3653565

    Thanks Phil.

    I have to fetch data only until friday 5 pm. So this expression gives me last friday( i need to remove -1 from my expression) with current timestamp but i want until 5 pm only. I hope u understand now. How to achieve that?? Thanks.

  • June 13, 2019 at 10:43 pm

    #3653567

    So you want an expression which returns a datetime of 5pm last Friday, is that it?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • June 13, 2019 at 10:44 pm

    #3653568

    Yeah. Thats correct Phil.

  • June 13, 2019 at 11:57 pm

    #3653591 

    -- SV: For understanding getdate() and things you need
    select SYSDATETIME() as sysdatetime_FYI
    , getdate() as getdate_FYI
    , cast(getdate() as time) as getdate_TimeOnly
    , CONVERT(varchar(15), cast(getdate() as time),100) as getdate_TimeOnly_AMPM
    , DATEPART(HOUR, GETDATE())
    , DATEPART(WEEKDAY, GETDATE())

    -- SV: Your answer 
    SELECT 
    getdate() as getdate_FYI
    , 
    CASE 
    WHEN DATEPART(WEEKDAY, GETDATE()) > 5 -- Friday
    AND DATEPART(HOUR, GETDATE()) > 17
    THEN 
    DATEADD( HOUR, 17, 
    DATEADD(DAY, +4, DATEADD(WEEK, DATEDIFF(WEEK, 0, getdate()), 0)) 
    )
    ELSE 
    DATEADD( HOUR, 17, 
    DATEADD(DAY, -3, DATEADD(WEEK, DATEDIFF(WEEK, 0, getdate()), 0)) 
    )
    END YourAnswer
    , DATEADD( HOUR, 17, 
    DATEADD(DAY, +4, DATEADD(WEEK, DATEDIFF(WEEK, 0, getdate()), 0)) 
    ) ThisWeekFriday_5PM
    , DATEADD( HOUR, 17, 
    DATEADD(DAY, -3, DATEADD(WEEK, DATEDIFF(WEEK, 0, getdate()), 0)) 
    ) LastWeekFriday_5PM

  • June 14, 2019 at 12:05 am

    #3653594

    Thanks Sreedhar but this is T-SQL Way of doing it and I want it as SSIS expression way.

  • June 14, 2019 at 12:19 am

    #3653599

    sorry, Let me provide the SSIS ... I took your code and below article and come up with this, for Last Friday exact 5 pm:

    DATEADD("HH",17,(DT_DBTIMESTAMP)((DT_STR,30,1252)(DT_DBDATE)(DATEADD("dd", -1 - (DATEPART("dw", getdate()) % 7), getdate()))))

    clue: DATEPART("dw",GETDATE()) and went till to get the date and then added the hours.

    Please check whether this helps ...

    https://stackoverflow.com/questions/21704652/ssis-expression-for-date-of-previous-friday

    • This reply was modified 5 years, 6 months ago by  v_sreedhar.
    • This reply was modified 5 years, 6 months ago by  v_sreedhar.

  • June 14, 2019 at 3:33 am

    #3653633

    v_sreedhar wrote:

    -- SV: For understanding getdate() and things you need
    select SYSDATETIME() as sysdatetime_FYI
    , getdate() as getdate_FYI
    , cast(getdate() as time) as getdate_TimeOnly
    , CONVERT(varchar(15), cast(getdate() as time),100) as getdate_TimeOnly_AMPM
    , DATEPART(HOUR, GETDATE())
    , DATEPART(WEEKDAY, GETDATE())

    -- SV: Your answer 
    SELECT 
    getdate() as getdate_FYI
    , 
    CASE 
    WHEN DATEPART(WEEKDAY, GETDATE()) > 5 -- Friday
    AND DATEPART(HOUR, GETDATE()) > 17
    THEN 
    DATEADD( HOUR, 17, 
    DATEADD(DAY, +4, DATEADD(WEEK, DATEDIFF(WEEK, 0, getdate()), 0)) 
    )
    ELSE 
    DATEADD( HOUR, 17, 
    DATEADD(DAY, -3, DATEADD(WEEK, DATEDIFF(WEEK, 0, getdate()), 0)) 
    )
    END YourAnswer
    , DATEADD( HOUR, 17, 
    DATEADD(DAY, +4, DATEADD(WEEK, DATEDIFF(WEEK, 0, getdate()), 0)) 
    ) ThisWeekFriday_5PM
    , DATEADD( HOUR, 17, 
    DATEADD(DAY, -3, DATEADD(WEEK, DATEDIFF(WEEK, 0, getdate()), 0)) 
    ) LastWeekFriday_5PM

    Consider the following along with the fact that there is no reliance of the possibly changed "Weekday" value returned by SQL Server:

    --===== Find the previous (or current if "today") Friday at 5PM.
     SELECT DATEADD(dd,DATEDIFF(dd,4,GETDATE())/7*7,4)+'5:00PM';

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 14, 2019 at 12:44 pm

    #3653781

    Thanks Sreedhar... Ur expression works but the variable has datetime datatype so it converts it to 6/7/2019 5:00:00 PM format rather than 2019-06-07 17:00:00.000.  Anyway Thanks for all ur help so far. Thanks

    • This reply was modified 5 years, 6 months ago by  dallas13.

  • June 14, 2019 at 1:13 pm

    #3653802

    dallas13 wrote:

    Thanks Sreedhar... Ur expression works but the variable has datetime datatype so it converts it to 6/7/2019 5:00:00 PM format rather than 2019-06-07 17:00:00.000.  Anyway Thanks for all ur help so far. Thanks

    I don't know SSIS to any great extent.  In fact, my job in the past has been to replace SSIS packages with stored procedures and other T-SQL.

    That, notwithstanding, take a look at the formula that I posted to do this in T-SQL.  I'm sure that you can easily modify it for use in SSIS.  If it doesn't allow direct addition of time, as I did in the formula, just add it like you would in SSIS.  The key is that it accurately finds the previous Friday with the understanding that if "today" is a Friday, it will return "today".

    As for formatting the output, that should be done after the formula has resolved.  In T-SQL, the CONVERT function does this rather well.  Surely SSIS also has such a formatting function.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 14, 2019 at 2:23 pm

    #3653826

    Thanks, Jeff. The date processing capabilities in T-SQL are significantly more powerful than those built in to SSIS (though, of course, SSIS has native access to .NET libraries through C# and VB.NET, making most things possible with a little bit of code).

    In this case, v_sreedhar's expression is about as good as it gets, and that's really not very good at all, given the relative simplicity of the request (and its solution in T-SQL).

    As a result of the expression's complexity, for the sake of future maintenance, I would probably either

    1. Use T-SQL to do it, if other SQL Server operations are being performed by the package, or
    2. Do it in C#

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • June 14, 2019 at 11:14 pm

    #3654021

    Yowch.  Thanks for the info, Phil.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 15, 2019 at 3:14 pm

    #3654109

    This is a case where I would use C# in a script task to set variables - instead of trying to build it as an SSIS expression.  The C# code can be done in a single line:

    Dts.Variables["User::LastFriday"].Value = DateTime.Today.AddDays(-(int)(DateTime.Today.AddDays(-5).DayOfWeek)).Date.AddHours(17);

    It also depends on what the package is doing...and whether or not I have to connect to SQL Server for other configuration values.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • June 16, 2019 at 3:08 pm

    #3654256

    Jeffrey Williams wrote:

    It also depends on what the package is doing...and whether or not I have to connect to SQL Server for other configuration values.

    That same thought is usually the justification I use to convert SSIS packages to stored procedures. 😀

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 18 total)

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