T-SQL – How to get the Financial Quarter details of a date field

declare @table table
(
[Paid Date] date
)

insert into @table
values('20150102'),('20150512'),('20150830'),('20151231'),('20141230')

;WITH Quarters AS (
   SELECT Q = 'Q1', MonthBegin = 1, MonthEnd = 3 UNION
   SELECT Q = 'Q2', MonthBegin = 4, MonthEnd = 6 UNION
   SELECT Q = 'Q3', MonthBegin = 7, MonthEnd = 9 UNION
   SELECT Q = 'Q4', MonthBegin = 10, MonthEnd = 12
)
SELECT
   [paid date],[Quarter] = 'FSY'+CONVERT(VARCHAR(4), DATEPART(yyyy, CONVERT(DATETIME, [paid date]))) + '_' + q.Q
FROM
@table
  INNER JOIN Quarters q ON
      DATEPART(m, CONVERT(DATETIME, [paid date])) >= q.MonthBegin AND
      DATEPART(m, CONVERT(DATETIME, [paid date])) <= q.MonthEnd;

Output

QuaterDetails

 

 

Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in SQL and tagged , , , , . Bookmark the permalink.

4 Responses to T-SQL – How to get the Financial Quarter details of a date field

  1. Lionel Dejean says:

    Very sleek, I can use this

  2. Stephen says:

    This is a good start for me but most financial years down here run from 1 April thru 31 March, or, 1 July thru 30 June 😦

  3. Ray says:

    Here is an easier way to get your results using your sample table and data:

    select [Paid Date], convert(char(4),DATEPART(yyyy,[Paid Date])) + ‘_Q’ + convert(char(1),DATEPART(q,[Paid Date]))
    from @table

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s