0

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

  • 1
    I rubberducked the answer when I was trying to ask this question and had to type it out. I feel silly but figured I might as well share. – Matt Oct 18 '18 at 14:28
  • Why would you need the last second of the previous month for anything? Much better to say >= beginning of the month and < beginning of the next month. See the section on BETWEEN here and this post. – Aaron Bertrand Oct 18 '18 at 14:38
  • @AaronBertrand I do agree however as this value gets displayed in the report header the end users were having issues seeing the next month in a report that only displays data for last month. Its a visual thing for end users. I could also just drop the time but then they wonder if its midnight on the 30th or 11:59:59 of the 30th. – Matt Oct 18 '18 at 14:40
  • What if there is data on 11:59:59.9999999? Will they think that data is missing? If it's a report for February (or any range of days), why do you need to display time at all? I think your display is creating confusion unnecessarily. – Aaron Bertrand Oct 18 '18 at 15:07
  • If the data exists at that time it will still be included in the report as the query is looking for <= midnight of the first of the next month. This basically boils down to customer requirement. I did read through those linked articles which helped me understand some potential pitfalls but I don't think those apply here. if I didn't display time they would ask if that data for that last day is included. I might be able to refactor this just to show the month alone but if they ever change the range then that would not work – Matt Oct 18 '18 at 15:14
0

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)))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.