Month to date in SP
-
-
I want to create a Stored procedure that looks up data based off the last full month and also one for year to date data. These SP will fire as part as a script so there can be no user input regarding the dates. Is there a way to do this? Chris
Sure. Just put a condition like below for data from the last month
WHERE MONTH(dtmTransactionDate) = MONTH(DATEADD(m, -1, GETDATE())) AND YEAR(dtmTransactionDate) = YEAR(DATEADD(m, -1, GETDATE()))If you need to get specific date (e.g. starting from 15 of Jan to 15 of Feb) just add DAY condition to it. For year to date, just subtract 1 year from the current date using the DATEADD function. Edbert P. Sydney, Australia. -
Sure. Just put a condition like below for data from the last month
WHERE MONTH(dtmTransactionDate) = MONTH(DATEADD(m, -1, GETDATE())) AND YEAR(dtmTransactionDate) = YEAR(DATEADD(m, -1, GETDATE()))If you need to get specific date (e.g. starting from 15 of Jan to 15 of Feb) just add DAY condition to it. For year to date, just subtract 1 year from the current date using the DATEADD function. Edbert P. Sydney, Australia.If you are interested in the last full month and need the last day - you run into problems with February. Try: declare @month char(10),@lastmonth datetime set @month = Convert(Char(6),getdate(),112)+'01' Print @month select @lastmonth = dateadd(m,-1,cast(@month as datetime)) print @lastmonth --First day of prior month select @lastmonth = dateadd(d,-1,cast(@month as datetime)) print @lastmonth --Last day of prior month Barbara