<Resolved> Fetch last date of a month and last minute of that day-CodePudding

I need to get last day of the month with time like

2023-01-31 23:59:59:000000

I'm able to get only the last day of the month with time stamp as

2023-01-31 00:00:00:000000

CodePudding user response：

As jarlh said your best method is to add a day to the end of the month, then subtract a second (although if you really want the absolute maximum time I think you'd want to subtract 3 milliseconds).

EOMONTH -> Add 1 day -> Cast as datetime -> remove 1 second / 3 milliseconds. You have to cast as datetime because the EOMONTH function implicitly casts to a date

The code will be something like this:

SELECT DATEADD(SECOND, -1, CAST(DATEADD(DAY, 1, EOMONTH(@currentDate)) AS DATETIME))
SELECT DATEADD(MILLISECOND, -3, CAST(DATEADD(DAY, 1, EOMONTH(@currentDate)) AS DATETIME))

CodePudding user response：

There are already similar questions with a lot of answers. You should find your anwer for sure: Get the last day of the month in SQL

SQL query to display end date of current month

DECLARE @currentDate DATE = GETDATE()
SELECT EOMONTH (@currentDate) AS CurrentMonthED

SQL query to display end date of Next month

DECLARE @currentDate DATE = GETDATE()
SELECT EOMONTH (@currentDate, 1 ) AS NextMonthED