CODEWITHSUNDEEP
sqlsql-server
user4778
user4778

Reputation: 53

how to get automatically previous month date range in SQL?

I have 2012 SQL Server stored procedure ran automatically every fifth of the month but the problem I have is supplying previous month date range e.g. I need to have clause like ...where datein between '2014-02-01' and '2014-02-28' and next month it would change it to ...where datein between '2014-03-01' and '2014-02-31' and so on.

thanks

Upvotes: 5

Views: 37586

Answers (7)

Md. Nazmul Alom
Md. Nazmul Alom

Reputation: 209

Please try

DECLARE @date date=GETDATE()

SELECT DATEADD(DAY,1,EOMONTH(@date,-2)) AS StartDate, EOMONTH(@date,-1) AS EndDate

Upvotes: 0

user11658885
user11658885

Reputation: 113

It is bad practice to use getdate(), instead across all application use getutcdate()

WHERE @datein BETWEEN DATEADD(DAY,1,EOMONTH(GETUTCDATE(),-2)) AND EOMONTH(GETUTCDATE(),-1)

Upvotes: 1

Doug Thompson - DouggyFresh
Doug Thompson - DouggyFresh

Reputation: 470

might try this for Feb as original only went to 27th of Feb BETWEEN DATEADD(DAY,1,EOMONTH(GETDATE(),-2)) AND DATEADD(DAY,1,EOMONTH(GETDATE(),-1))

Upvotes: 0

user3011237
user3011237

Reputation: 31

This worked for my current version of mysql:

SELECT DATE_ADD(LAST_DAY(DATE_SUB(current_date, INTERVAL 2 MONTH)), INTERVAL 1 DAY) AS StartDate,
LAST_DAY(DATE_SUB(current_date, INTERVAL 1 MONTH)) AS EndDate;

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33581

Here is a way to do this so your dates will be greater than the beginning of this month and less than the beginning of next month.

datein >= dateadd(month, datediff(month, 0, getdate()), 0) --BeginningOfThisMonth
and datein < dateadd(month, datediff(month, 0, getdate()) + 1, 0) --BeginningOfNextMonth

Upvotes: 0

Dbloch
Dbloch

Reputation: 2376

This should work

SELECT DATEADD(DAY,1,EOMONTH(GETDATE(),-2)) AS StartDate, EOMONTH(GETDATE(),-1) AS EndDate

To be more specific in your WHERE clause

WHERE @datein BETWEEN DATEADD(DAY,1,EOMONTH(GETDATE(),-2)) AND EOMONTH(GETDATE(),-1)

Upvotes: 17

Gordon Linoff
Gordon Linoff

Reputation: 1271241

You can use getdate() and some date arithmetic. Here is a relatively easy way:

where datein >= cast(dateadd(month, -1, getdate() - day(getdate()) + 1) as date) and
      datein < cast(getdate() - day(getdate()) + 1)

The key idea here is to subtract the day of the month and add 1 to get the first day of the current month.

Upvotes: 1

Related Questions