CODEWITHSUNDEEP
sql-serverreporting-services
user1777929
user1777929

Reputation: 797

SSRS How to define parameters as 7 days ago plus 1 hour

I have a report made using SSRS and SQL Server and I have set a StartDate and EndDate parameters for the report.

I have set the default value for StartDate as =DateAdd("D", -7, Today()) I have set the default value for EndDate as =DateAdd("H", 1, Today())

How can I add 1 hour to the StartDate Parameter?

I've tried

=DateAdd("H", 1, (DateAdd("D", -7, Today())))

Would that work? I'm currently testing it but the report usually takes 6 hours to run.

Upvotes: 1

Views: 3527

Answers (2)

J Weezy
J Weezy

Reputation: 3957

Would that work? You can always try that in SQL Server. The answer is yes :o)

Although, you should use the NOW() function instead of the TODAY() function (see the Update below).

SQL Server:

SELECT DATEADD(HH, 1, DATEADD(DD, -7, GETDATE()))

SSRS/VS:

DateAdd(DateInterval.Hour, -167, Now())

Update: To Larnu's point, Today() will default as a date format of MM/DD/YYYY. If you only subtract 167 hours from Today, then the field will show in date time, but the result will be incorrect as it will show 1:00 AM for the time. So use the NOW() function, instead of TODAY(). The result will be 167 hours from the current time and will be in the format of MM/DD/YYYY HH:MM:SS.

Also, you can make the change in the parameter field and click on the Preview in VS to see these changes. You would not need to run the report to see this change. That, or I do not understand what you are trying to accomplish.

Upvotes: 0

Adam
Adam

Reputation: 4178

Do the date math in hours instead of days.

=DateAdd("H", -169, Today())

Upvotes: 2

Related Questions