locked
SSRS tweak to time expression RRS feed

  • Question

  • Hello

    I am currently using this expression to show the previous working day in SSRS:

    =DateAdd("d"
        , Switch(DatePart("w", Today) = 2, -3
            ,DatePart("w", Today) = 1, -2
            ,True, -1)
        , Today)

    which works fine.

    However I would like the output to be,if I ran the query today for example,:

    23/04/2020 23:59:59

    Instead of the current 23/04/2020

    Please can you advise on how I could add hours, minutes and seconds- 23:59:59 - to the above expression?

    Thank you





    Friday, April 24, 2020 9:47 AM

All replies

  • Hello William,

    I tested your expression in my machine, the output is 24/04/2020  12:00:00AM.

    Normally, Today will return the date and time, you can try to use Now instead of Today. If you just want to return 23:59:59, it is not impossible to completed by DateAdd.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 27, 2020 8:55 AM
  • Hi William,

    The date to which you add the offset should have a time part, meaning the solution is:

    =DateAdd("d"
        , Switch(DatePart("w", Today) = 2, -3
            ,DatePart("w", Today) = 1, -2
            ,True, -1)
        , Now)

    Monday, April 27, 2020 10:15 AM
  • thanks for taking the trouble to come back

    NOW is a good idea but I need the date to show the previous working day - e.g if I run it now it needs to show Friday's date plus 23:59:59 - not Friday's date and the time as it is when the report is run.

    Monday, April 27, 2020 11:36 AM
  • thanks for taking the trouble to come back

    NOW is a good idea but I need the date to show the previous working day - e.g if I run it now it needs to show Friday's date plus 23:59:59 - not Friday's date and the time as it is when the report is run.

    Monday, April 27, 2020 11:36 AM
  • Hello William,

    Thanks for your clarification. 

    After doing some research, unfortunately, DataAdd function can not make it.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 28, 2020 1:12 AM