Calculate last friday date with timestamp ssis

  • dallas13

    SSCertifiable

    Points: 7491

    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

  • Phil Parkin

    SSC Guru

    Points: 243939

    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 7 months, 2 weeks ago by  Phil Parkin. Reason: Additional text

    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.

  • dallas13

    SSCertifiable

    Points: 7491

    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.

  • Phil Parkin

    SSC Guru

    Points: 243939

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

    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.

  • dallas13

    SSCertifiable

    Points: 7491

    Yeah. Thats correct Phil.

  • v_sreedhar

    Grasshopper

    Points: 14

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

    SSCertifiable

    Points: 7491

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

  • v_sreedhar

    Grasshopper

    Points: 14

    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 7 months, 2 weeks ago by  v_sreedhar.
    • This reply was modified 7 months, 2 weeks ago by  v_sreedhar.
  • Jeff Moden

    SSC Guru

    Points: 995503

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

  • dallas13

    SSCertifiable

    Points: 7491

    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 7 months, 2 weeks ago by  dallas13.
  • Jeff Moden

    SSC Guru

    Points: 995503

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

  • Phil Parkin

    SSC Guru

    Points: 243939

    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#

    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.

  • Jeff Moden

    SSC Guru

    Points: 995503

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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)

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88219

    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.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • Jeff Moden

    SSC Guru

    Points: 995503

    Jeffrey Williams 3188 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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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 19 total)

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