Sql-server – How to get the last second of the last month as a default parameter value

datetimesql serversql-server-2008-r2ssrs-2008-r2

I am trying to get the last second of the last month as a default value for a parameter. I can get the last day of the last month from this query on social.msdn.microsoft.com

last month last day expression: DateValue(DateAdd("D",-1,DateAdd("D",-(Day(Now)-1),Now)))

The about would return 12am of the last day of the month. Starting with that I made one change so that I can get the last second of the last month.

=DateValue(DateAdd("S",-1,DateAdd("D",-(Day(Now)-1),Now)))

That does not work though as the time is still set to midnight.

How do I get the last second of the last month as a datetime value?


This has no affect on underlying queries that deal with report data. This is purely about showing an end user the start and end dates in a report header. "9/1/2018 00:00:00 to 9/30/2018 11:59:59" "9/1/2018 00:00:00 to 10/1/2018 00:00:00". The former example to my end users is clearer that the data is in the month where as seeing number for the next month (like in the second example) is … unsettling

Best Answer

They key is the placement of DateValue() which:

Returns a Date value containing the date information represented by a string, with the time information set to midnight (00:00:00).

The problem with where you are removing seconds is inside the datevalue() function which is dropping the time portion.

It is still important as you want time removed from the initial calculation for getting the last day of the month.

A little shifting around get the desired output.

=DateAdd(DateInterval.Second,-1,DateValue(DateAdd(DateInterval.Day,-(Day(Now)-1),Now)))

or

=DateAdd("s",-1,DateValue(DateAdd("d",-(Day(Now)-1),Now)))