Fordraiders
asked on
get a date range going back 3 months from the first day in the previous month
DECLARE @PREVIOUSFIRSTDAYMONTH AS DATE --First day of previous month
DECLARE @PREVIOUSLASTDAYMONTH AS DATE --Last Day of previous month
DECLARE @TODAY AS DATE
SET @TODAY = GETDATE()
SET @PREVIOUSFIRSTDAYMONTH = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) --First day of previous month
SET @PREVIOUSLASTDAYMONTH = DATEADD(MONTH, DATEDIFF(MONTH, -3, GETDATE())-1, -1) --Last Day of previous 3 months
I'm to get this code to start from the first day of the previous month and then go back 3 months ?
Thanks
fordraiders
DECLARE @PREVIOUSLASTDAYMONTH AS DATE --Last Day of previous month
DECLARE @TODAY AS DATE
SET @TODAY = GETDATE()
SET @PREVIOUSFIRSTDAYMONTH = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) --First day of previous month
SET @PREVIOUSLASTDAYMONTH = DATEADD(MONTH, DATEDIFF(MONTH, -3, GETDATE())-1, -1) --Last Day of previous 3 months
I'm to get this code to start from the first day of the previous month and then go back 3 months ?
Thanks
fordraiders
To understand your formulas you should know what means 0 in DATEDIFF(MONTH, 0, GETDATE()) …
DATEDIFF calculates difference between two dates in units given by the first parameter. So you are calculating difference in MONTHs between the current date and the date 0 . What means the zero is showed in this query:
SELECT CAST(0 AS datetime) -- you should see 1900-01-01 00:00
Another formula contains -3 instead of 0 so try this:
SELECT CAST(-3 AS datetime)
and now the algorithm should be explained.
DATEDIFF calculates difference between two dates in units given by the first parameter. So you are calculating difference in MONTHs between the current date and the date 0 . What means the zero is showed in this query:
SELECT CAST(0 AS datetime) -- you should see 1900-01-01 00:00
Another formula contains -3 instead of 0 so try this:
SELECT CAST(-3 AS datetime)
and now the algorithm should be explained.
DECLARE @PREVIOUSFIRSTDAYMONTH AS DATE --First day of 3rd previous month
DECLARE @PREVIOUSLASTDAYMONTH AS DATE --Last Day of previous month
DECLARE @TODAY AS DATE = GETDATE()
SET @PREVIOUSFIRSTDAYMONTH = DATEFROMPARTS(YEAR(DATEADD(MONTH, -3, @TODAY)), MONTH(DATEADD(MONTH, -3, @TODAY)), 1)
SET @PREVIOUSLASTDAYMONTH = DATEADD(DAY, -1, DATEFROMPARTS(YEAR(@TODAY), MONTH(@TODAY), 1))
SELECT @PREVIOUSFIRSTDAYMONTH, @PREVIOUSLASTDAYMONTH
ASKER
@mate'.
DATEFROMPARTS is not recognized ? i'm in sql 2008 r2
DATEFROMPARTS is not recognized ? i'm in sql 2008 r2
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks very much !!
DECLARE @PREVIOUSFIRSTDAYMONTHMINU
SET @PREVIOUSFIRSTDAYMONTHMINU