Report Builder Expression for quarter and year at specific times

Question

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

Answer 1

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

Answer 2

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.