Report Builder Expression for quarter and year at specific times

Tamayo, Ashley 121 Reputation points
2021-03-04T21:48:19.303+00:00

For starting and ending the quarter I have the following expressions:

=dateadd("q",datediff("q",1/1/1900",today())-1,"1/1/1900")
=dateadd("s",-1,dateadd("q",datediff("q","1/1/1900",today()),"1/1/1900"))

For starting and ending the last year I have the following expressions:

=dateserial(year(today())-1,1,1)
=dateserial(year(today()),1,1)

I am trying to find how to specify I want the quarter and year to start at 07:00:00 and end at 07:00:00.

The result for last quarter would start 10/1/2020 07:00:00 and end 01/01/2021 07:00

The result for the last year would start 01/01/2020 07:00:00 and end 01/01/2021 07:00:00

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,860 questions
0 comments No comments
{count} votes

Accepted answer
  1. ZoeHui-MSFT 34,996 Reputation points
    2021-03-05T02:39:49.547+00:00

    Hi anonymous user,

    As @Michael Taylor said we could use DateAdd to meet your requirement.

    Just modify the expression to fit SSRS, the expression should be like below:

    =DateAdd(DateInterval.Hour, 7, DateSerial(year(today()),1,1))  
    =DateAdd(DateInterval.Hour, 7, dateserial(year(today())-1,1,1))  
    

    Regards,

    Zoe


    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues October

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Michael Taylor 50,916 Reputation points
    2021-03-04T22:41:00.943+00:00

    Use DateAdd to add the 7 hours to the date you generated with DateSerial.

       DateAdd("hh", 7, DateSerial(...))  
    

    For the starting and ending quarter you're using Today which returns the current date with a time of midnight so it should work with DateAdd as well. However if you were working with a date that had a time then you'd need to reset the time first.

    0 comments No comments