Reputation: 53
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
Reputation: 209
Please try
DECLARE @date date=GETDATE()
SELECT DATEADD(DAY,1,EOMONTH(@date,-2)) AS StartDate, EOMONTH(@date,-1) AS EndDate
Upvotes: 0
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
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
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
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
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
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