Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2007
    Posts
    127

    Unanswered: Financial Quarter from DateField

    How do i get the financial quarter data from the following date field in my table?

    DateField
    01/01/2010

    I would like "0910 Q4"

    Our financial quarters run from April to March

    I've got this so far

    SELECT

    CASE
    WHEN DATEPART ( MM ,( CONVERT ( DateTime , [DateField] , 103 ))) IN ( '1' , '2' , '3' )
    THEN CONVERT(VARCHAR(100), DATEPART ( yy ,( CONVERT ( DateTime , [DateField , 103 ))) ) + 'Q4'
    END AS Qtr
    FROM My Table

    Instead of producing "2009Q4" it should produce "0809Q4" based on this date 18/03/2009, how do i do this

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Not sure if this is what you need, but here it is anyway:
    Code:
    if object_id('dbo.fn__MonthWithOffset') is not null
       drop function dbo.fn__MonthWithOffset
    go
    create function dbo.fn__MonthWithOffset (
       @monthNumber tinyint
      ,@monthOffset tinyint
      ,@operation   char(1) /* + or - */    ) returns tinyint
    as begin
          declare @result tinyint
          if @operation not in ('+','-')
             return (null)
          if @monthNumber not between 1 and 12
             return (null)
          if @operation = '+' begin
             if (cast(@monthNumber as int) + cast(@monthOffset as int)) > 12
                set @result = cast((cast(@monthNumber as int) + cast(@monthOffset as int)) % 12 as tinyint)
             else
                set @result = @monthNumber + @monthOffset
          end else if @operation = '-' begin
             if @monthOffset > 12
                set @monthOffset = cast((@monthOffset % 12) as tinyint)
             if (cast(@monthNumber as int) - @monthOffset) <= 0
                set @result = 12 - (@monthOffset - @monthNumber)
             else
                set @result = @monthNumber - @monthOffset
          end
          return (@result)
       end
    go
    select dbo.fn__MonthWithOffset (12, 255, '+')
    if object_id('dbo.fn__FinancialQuarter') is not null
       drop function dbo.fn__FinancialQuarter
    go
    create function dbo.fn__FinancialQuarter (
       @date datetime
      ,@firstMonth tinyint                   ) returns tinyint
    as begin
          return (
             select
                case datepart(month, @date)
                   when dbo.fn__MonthWithOffset(@firstMonth, 0, '+')  then 1
                   when dbo.fn__MonthWithOffset(@firstMonth, 01, '+') then 1
                   when dbo.fn__MonthWithOffset(@firstMonth, 02, '+') then 1
                   when dbo.fn__MonthWithOffset(@firstMonth, 03, '+') then 2
                   when dbo.fn__MonthWithOffset(@firstMonth, 04, '+') then 2
                   when dbo.fn__MonthWithOffset(@firstMonth, 05, '+') then 2
                   when dbo.fn__MonthWithOffset(@firstMonth, 06, '+') then 3
                   when dbo.fn__MonthWithOffset(@firstMonth, 07, '+') then 3
                   when dbo.fn__MonthWithOffset(@firstMonth, 08, '+') then 3
                   when dbo.fn__MonthWithOffset(@firstMonth, 09, '+') then 4
                   when dbo.fn__MonthWithOffset(@firstMonth, 10, '+') then 4
                   when dbo.fn__MonthWithOffset(@firstMonth, 11, '+') then 4
                end
          )
       end
    go
    select
       Qtr = right('00'+cast(month(myDate) as varchar(2)), 2)
      +substring(right('0000'+cast(year(myDate) as varchar(4)), 4), 3, 2)
      +' Q' + cast(dbo.fn__FinancialQuarter (myDate, 4) as char(1))
       from (
          select myDate = '20090614' union all
          select '20090918' union all
          select '20100126' union all
          select '20090330' union all
          select '20091209' ) x
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here you go --
    Code:
    SELECT DateField
         , RIGHT(YEAR(DATEADD(M,-3,DateField)),2)
          +RIGHT(YEAR(DATEADD(M,+9,DateField)),2)
          +'Q'+CAST(((MONTH(DateField)+8)/3)%4+1 AS CHAR) AS Qtr
      FROM daTable
    tested
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •