# Thread: Financial Quarter from DateField

1. Registered User
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. Registered User
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)) &#37; 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```

3. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
here you go --
Code:
```SELECT DateField