CODEWITHSUNDEEP
sql-server
user2447136
user2447136

Reputation: 199

Get last day of previous month in SQL

I am trying to get a function for the last day of the previous month without the time show, that is, 2019-09-30 for last month.

This is the code I have tried

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

expected results now 2019-09-30 actual results

Upvotes: 1

Views: 18097

Answers (4)

Nikunj Satasiya
Nikunj Satasiya

Reputation: 1053

You should try this way as I showed in the example below.

Solution 1:

Last Day of Previous Month

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
LastDay_PreviousMonth

Solution 2:

Last Day of Current Month

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
LastDay_CurrentMonth

Solution 3:

Last Day of Next Month

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))
LastDay_NextMonth

Upvotes: 1

Zhorov
Zhorov

Reputation: 30023

You should use EOMONTH() function, which return type is date, while the return type for DATEADD() is dynamic and depends on the argument supplied for date. In your case the result from DATEADD(s,-1, DATEADD(mm, DATEDIFF(m,0,GETDATE()), 0)) is datetime data type, because 0 is implicitly converted to datetime value 01/01/1900 00:00:00 (implicit conversion between int and date is not allowed).

SELECT EOMONTH(DATEADD(month, -1, GETDATE()))
-- or
SELECT EOMONTH(GETDATE(), -1)

Upvotes: 4

Cato
Cato

Reputation: 3701

If you take the day of today's date, and subtract it from today's date, it will give you the last day of the previous month.

SELECT DATEADD(DY, -DAY(getdate()), cast(getdate() as date))

note the cast to 'DATE' type to give date only

Upvotes: 1

dswdsyd
dswdsyd

Reputation: 616

try using the EOMONTH function with an offset:

SELECT EOMONTH('2019-10-29', -1)

Upvotes: 6

Related Questions