CODEWITHSUNDEEP
sql-servert-sql
Fire Hand
Fire Hand

Reputation: 26416

Get the date one month before and it's always the last day of the month

In my db, I have a column, 'Transaction Date' with datetime datatype. For instance, '2011-05-31 00:00:00.000'. I would like to create a SQL Query by selecting data with whereby the 'Transaction Date' column date is one month before the @InputDate.

I have tried with...
DATEADD(MONTH,-1,@InputDate) and it returns '30-May-2011', which is not what i want!
I want the value returns will always be the last day of the month like '31-May-2011'

Upvotes: 1

Views: 17182

Answers (5)

EstevaoLuis
EstevaoLuis

Reputation: 2548

Since SQL Server 2012 you can use the EOMONTH built-in function to get the last day of a month.

So, to get the last day of the previous month you can use this query:

SELECT EOMONTH(GETDATE(), -1)

Where instead of GETDATE() you can put your Date var.

SELECT EOMONTH(@InputDate, -1)

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44336

Last day of same month

SELECT DATEADD(m, DATEDIFF(m, -1, '2011-05-31'), -1)

Last day of last month

SELECT DATEADD(m, DATEDIFF(m, 0, '2011-05-31'), -1)

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

I think you're asking, given any date, for the last day of the previous month?

If so, the following works (where CURRENT_TIMESTAMP is being used as the date to search from, '20010101' and '20001231' are constants):

select DATEADD(month,DATEDIFF(month,'20010101',CURRENT_TIMESTAMP),'20001231')

It works because the relationship between the two constant dates is that, compared to '20010101', '20001231' was the last date of the month before.

Upvotes: 0

Abdul Rahman
Abdul Rahman

Reputation: 712

Use the following scripts:

Last Day of Previous Month:

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

Last Day of Current Month:

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

Last Day of Next Month:

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

Last Day of Any Month and Year:

DECLARE @dtDate DATETIME
SET @dtDate = '8/18/2007'
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))
LastDay_AnyMonth

Upvotes: 7

Gatekeeper
Gatekeeper

Reputation: 1616

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

GETDATE() can be replaced by your input date

Upvotes: 2

Related Questions