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
0 comments:
Post a Comment