June 18, 2010 at 7:09 pm
Hi friends,
How can i calculate quarters for the past 10 years.
Thanks,
Sam
June 18, 2010 at 8:10 pm
Sahasam (6/18/2010)
Hi friends,How can i calculate quarters for the past 10 years.
Thanks,
Sam
Hi Sam
Do you want calculate quarters between 2 dates
This is my code.
DECLARE @NewDate Datetime, @OldDate Datetime
SELECT @NewDate = 'Jan 1, 2010', @OldDate = 'Dec 10, 1999'
SELECT (YEAR(@NewDate) - YEAR(@OldDate)) * 4 + (DATEPART(QUARTER, @NewDate) - DATEPART(QUARTER, @OldDate))
June 18, 2010 at 8:37 pm
I want to display all those quarter end dates for the past 10 years.
Current year should be Year to date quarter
and all past 9 year quarters.
Example: output should look like below:
2010-03-31 00:00:00.000
2009-12-31 00:00:00.000
2009-09-30 00:00:00.000
2009-06-30 00:00:00.000
2009-03-31 00:00:00.000
2008-12-31 00:00:00.000
2008-09-30 00:00:00.000
2008-06-30 00:00:00.000
2008-03-31 00:00:00.000
2007-12-31 00:00:00.000
2007-09-30 00:00:00.000
2007-06-30 00:00:00.000
2007-03-31 00:00:00.000
2006-12-31 00:00:00.000
2006-09-30 00:00:00.000
2006-06-30 00:00:00.000
2006-03-31 00:00:00.000
2005-12-31 00:00:00.000
2005-09-30 00:00:00.000
2005-06-30 00:00:00.000
2005-03-31 00:00:00.000
2004-12-31 00:00:00.000
2004-09-30 00:00:00.000
2004-06-30 00:00:00.000
2004-03-31 00:00:00.000
2003-12-31 00:00:00.000
2003-09-30 00:00:00.000
2003-06-30 00:00:00.000
2003-03-31 00:00:00.000
2002-12-31 00:00:00.000
2002-09-30 00:00:00.000
2002-06-30 00:00:00.000
2002-03-31 00:00:00.000
2001-12-31 00:00:00.000
2001-09-30 00:00:00.000
2001-06-30 00:00:00.000
2001-03-31 00:00:00.000
June 19, 2010 at 8:22 am
Guys,
I came up with a solution.
DECLARE @STARTDATE DATETIME
SELECT @STARTDATE = '01-JAN-2001'
SELECT DATEADD(MM,NUMBER*3,@STARTDATE)-1
FROM master.DBO.spt_values
WHERE TYPE = 'P'
AND number between 1 and (( DATEDIFF(YY,@startdate,getdate())+1)*4)
AND DATEADD(MM,NUMBER*3,@STARTDATE)-1 <= GETDATE()
ORDER BY 1 DESC
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply