0

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
Vasanth
  • 1
  • 2
  • 3
    Step back one second from the first day of the following month? – jarlh Jan 13 '23 at 07:24
  • Yes Jarlh, exactly – Vasanth Jan 13 '23 at 07:26
  • How are you going to use that date time value `2023-01-31 23:59:59:000000` ? – Squirrel Jan 13 '23 at 08:16
  • As Squirrel asked, why do you need it? Normally you would use a condition like `where DateTimeColumn < Tomorrow` rather than `where DateTimeColumn <= LastSecondOfTheDay` – Dale K Jan 13 '23 at 08:58
  • Hi Dale, im need that query to read a particular column data untill the last minute of the month. i got this DATEADD(ss,-1,(DATEADD(mm,DATEDIFF(m,0,GETDATE())+1,0))) this worked actually – Vasanth Jan 13 '23 at 19:13

2 Answers2

0

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))
RickyTillson
  • 323
  • 1
  • 2
  • 10
  • 1
    If you use the recommended datetime datatype, `datetime2` you can have milliseconds, rather than 3 milliseconds. – Dale K Jan 13 '23 at 08:57
  • i got this DATEADD(ss,-1,(DATEADD(mm,DATEDIFF(m,0,GETDATE())+1,0))) and it worked Dale thanks for your solutions – Vasanth Jan 13 '23 at 19:14
-1

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
Vanessa
  • 1
  • 3