DateADD function for Quarter in SQL Server 2005

Recently I have read few questions in forums about limitations of DATEADD function. Support I have one date 30-Mar-09 and I want to find last quarter of it. What should I do?

select CONVERT(VARCHAR,dateadd(q, 1, ‘2009-03-30’),106)

above query will display December as last quarter but what If I enter date like 10-Jan-09?

select CONVERT(VARCHAR,dateadd(q, 1, ‘2009-01-10’),106)

It will still show October as a last quarter of date but my need is, if I enter Jan, Feb or March, my quarter month should be December. In that case, I have found one solution which I am going to share with you. If there is any better alternative, I would like my reader to share it here so that people can use it.

select CASE datepart(m,‘2009-07-28’)

WHEN 1 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-03-31’),106)

WHEN 2 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-03-31’),106)

WHEN 3 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-03-31’),106)

WHEN 4 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-06-30’),106)

WHEN 5 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-06-30’),106)

WHEN 6 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-06-30’),106)

WHEN 7 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-09-30’),106)

WHEN 8 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-09-30’),106)

WHEN 9 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-09-30’),106)

WHEN 10 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-12-31’),106)

WHEN 11 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-12-31’),106)

WHEN 12 THEN CONVERT(VARCHAR,dateadd(q, 1, ‘2009-12-31’),106)

END

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com