CODEWITHSUNDEEP
sqlsql-servert-sqldatetimesql-date-functions
user2447136
user2447136

Reputation: 189

Get last day of previous month in yyyy-mm-dd format

How do I get the last day of the previous month in yyyy-mm-dd format?

This is what I have tried, however I don't want the seconds showing. Only YYYY-MM-DD:

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

Upvotes: 1

Views: 4520

Answers (6)

Salman Arshad
Salman Arshad

Reputation: 272066

Use the EOMONTH function along with FORMAT:

SELECT FORMAT(EOMONTH(CURRENT_TIMESTAMP, -1), 'yyyy-MM-dd')

The -1 in the above example means subtract 1 month from argument 1 (there is no need for DATEADD).

Upvotes: 3

Attie Wagner
Attie Wagner

Reputation: 1362

I would use this, due to newer versions of SQL supporting it:

SELECT  CONVERT(DATE,EOMONTH(DATEADD(MM,-1,GETDATE())),103)

Code is shorter to accomplish the same task...

Upvotes: 0

adina
adina

Reputation: 71

try this:

Select CONVERT(varchar, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)),23)

Upvotes: 0

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

Try this which is popular : 

 SELECT CONVERT(VARCHAR(10),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)),120)

Live demo

Upvotes: 0

Joaquín
Joaquín

Reputation: 1126

this should work

 SELECT CONVERT(VARCHAR(10),  DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)), 120)

Upvotes: 1

Anoos
Anoos

Reputation: 186

Convert it into date only format

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

Upvotes: 1

Related Questions