Setting datepart

[robert030975]

Hi

Our next fiscal year begins 26/04/09 to 24/04/10 Sun to Sat
if I use datepart (wk, date) -17 26/04/09 begins as week 1.
however on the 01/01/10 it goes to -17 instead of 36.


Is there anyway you can set what datepart starts at so it handles the rollover of year. bear in mind I have upto 2050.

 

[RiverGuy]

My suggestion is to create a Calendar reference table. Store the Date as the primary key, and have columns to include Fiscal Week, Fiscal Month, Fiscal Trimester, Fiscal Quarter, Fiscal Semester, Fiscal Year, etc. for each date. Then just join to that table any time you need to get your fiscal information.

 

[robert030975]

If you have 2005 run this and you see what I mean, if I can set what Datepart starts at then it should handle the year roll over otherwise I will have to do 3 selects.

one upto 31/12/09
one for 01 & 02/01/10
one for 03/01/10 onwards to 24/04/10.



WITH calendar AS
(

SELECT cast('2009-04-26' as datetime) Date
UNION ALL
SELECT Date + 1
FROM calendar
WHERE Date + 1 < '2050-12-31'
)
SELECT '1'+ convert(varchar,date,12) as caldate,
Convert (varchar, date, 103) as date_desc,
Date,
DATENAME (weekday, date) AS day,
DATEPART(wk ,Date)-17 as fiscal_week,

'1'+ convert(varchar,date,12)-7 as lw_date,

DateDiff(Day, -2, date) as dateno
FROM calendar
OPTION (MAXRECURSION 0)

 

[RiverGuy]

I don't know of any manipulations for the week of year DATEPART similar to DATEFIRST for day of week.

But to reiterate my previous reply, it's not uncommon at all to have a date table to translate calendar dates to fiscal dates. It might take a couple of hours to build and quite a few update statements, but once you have the table built, it's a very valuable asset.

 

[RyanEK]

Try the FiscalWeek UDF described here FiscalWeek

Ryan

 

[r937]

please give serious consideration to riverguy's calendar table suggestion

will save you much heartache and make all your queries trivially easy to code and exceptionally fast

r937.com | rudy.ca

 

[robert030975]

Okay its not pretty!!! but for anyone wanting to create a fiscal calendar table for the next few years this might be worth a look.


function ( big Thanks To RyanEK) for pointing me the right direction as this has proved to be a very useful function.

Okay so

fiscal year starts at 27 and uses default of Sunday being the first day.

function

CREATE function FiscalWeek (@startMonth varchar(2), @myDate datetime)
returns int
as
begin
declare @firstWeek datetime
declare @weekNum int
declare @year int
set @year = datepart(year, @myDate)+1
--Get 4th day of month of next year, this will always be in week 1
set @firstWeek = convert(datetime, str(@year)+@startMonth+'27', 102)
--Retreat to beginning of week
set @firstWeek = dateadd(day, (1-datepart(dw, @firstWeek)), @firstWeek)
while @myDate < @firstWeek --Repeat the above steps but for previous year
begin
set @year = @year - 1
set @firstWeek = convert(datetime, str(@year)+@startMonth+'27', 102)
set @firstWeek = dateadd(day, (1-datepart(dw, @firstWeek)), @firstWeek)
end
set @weekNum = (@year*100)+((datediff(day, @firstweek, @myDate)/7)+1)
return @weekNum
end


selection

WITH calendar AS
(

SELECT cast('2009-04-26' as datetime) Date
UNION ALL
SELECT Date + 1
FROM calendar
WHERE Date + 1 < '2010-04-25'
)
--insert into i_calendar
SELECT '1'+ convert(varchar,date,12) as caldate,
Convert (varchar, date, 103) as date_desc,
Date,
DATENAME (weekday, date) AS day,
dbo.FiscalWeek('04',date)%100 Fiscal_week,
dbo.FiscalWeek('04',date -7)%100 LW_fiscal_week,
Substring (CAST(dbo.FiscalWeek('04',date -7) as Varchar), 1,4) as LW_Fiscal_Year_start,
'1'+ convert(varchar,date,12)-7 as lw_date,
Fiscal_period =
CASE
WHEN dbo.FiscalWeek('04',date)%100 <= 4 THEN 1
WHEN dbo.FiscalWeek('04',date)%100 <= 8 THEN 2
WHEN dbo.FiscalWeek('04',date)%100 <= 13 THEN 3
WHEN dbo.FiscalWeek('04',date)%100 <= 17 THEN 4
WHEN dbo.FiscalWeek('04',date)%100 <= 21 THEN 5
WHEN dbo.FiscalWeek('04',date)%100 <= 26 THEN 6
WHEN dbo.FiscalWeek('04',date)%100 <= 30 THEN 7
WHEN dbo.FiscalWeek('04',date)%100 <= 34 THEN 8
WHEN dbo.FiscalWeek('04',date)%100 <= 39 THEN 9
WHEN dbo.FiscalWeek('04',date)%100 <= 43 THEN 10
WHEN dbo.FiscalWeek('04',date)%100 <= 47 THEN 11
Else 12
END,
Fiscal_period_name =
CASE
WHEN dbo.FiscalWeek('04',date)%100 <= 4 THEN 'May'
WHEN dbo.FiscalWeek('04',date)%100 <= 8 THEN 'Jun'
WHEN dbo.FiscalWeek('04',date)%100 <= 13 THEN 'Jul'
WHEN dbo.FiscalWeek('04',date)%100 <= 17 THEN 'Aug'
WHEN dbo.FiscalWeek('04',date)%100 <= 21 THEN 'Sep'
WHEN dbo.FiscalWeek('04',date)%100 <= 26 THEN 'Oct'
WHEN dbo.FiscalWeek('04',date)%100 <= 30 THEN 'Nov'
WHEN dbo.FiscalWeek('04',date)%100 <= 34 THEN 'Dec'
WHEN dbo.FiscalWeek('04',date)%100 <= 39 THEN 'Jan'
WHEN dbo.FiscalWeek('04',date)%100 <= 43 THEN 'Feb'
WHEN dbo.FiscalWeek('04',date)%100 <= 47 THEN 'Mar'
Else 'Apr'
END ,
--DATEPART(Quarter ,Date) AS Quarter,
Fiscal_quarter =
CASE
WHEN dbo.FiscalWeek('04',date)%100 <= 13 THEN 1
WHEN dbo.FiscalWeek('04',date)%100 <= 26 THEN 2
WHEN dbo.FiscalWeek('04',date)%100 <= 39 THEN 3
ELSE 4
END,
Fiscal_quarter_name =
CASE
WHEN dbo.FiscalWeek('04',date)%100 <= 13 THEN 'Q1'
WHEN dbo.FiscalWeek('04',date)%100 <= 26 THEN 'Q2'
WHEN dbo.FiscalWeek('04',date)%100 <= 39 THEN 'Q3'
ELSE 'Q4'
END,
Substring (CAST(dbo.FiscalWeek('04',date) as Varchar), 1,4) as fiscal_year_start,
Substring (CAST(dbo.FiscalWeek('04',date) as Varchar), 3,2)
+ ' '
+ Cast ((Substring (CAST(dbo.FiscalWeek('04',getdate()) as varchar), 3,2)) +2 as Varchar) as Fiscal_year,
DateDiff(Day, -2, date) as dateno,--,
DATENAME( month,Date) AS MonthName,
DATEPART(m ,Date) as MonthNo
FROM calendar
OPTION (MAXRECURSION 0)

my assumpitons are that both fiscal period and quarter are static and fiscal period operates on i.e. 4 4 5 in terms of a period and three periods to a quarter the only way I could think of how to do this was to use CASE thoughits a bit messy.

However if anyone else has a cleaner way of doing it I would be more than willing to try it.

Many Thanks to all

 

[robert030975]

sorry code correction
Substring (CAST(dbo.FiscalWeek('04',date) as Varchar), 3,2)
+ ' '
+ Cast ((Substring (CAST(dbo.FiscalWeek('04',getdate()) as varchar), 3,2)) +2 as Varchar) as Fiscal_year,

should be

Substring (CAST(dbo.FiscalWeek('04',date) as Varchar), 3,2)
+ ' '
+ Cast ((Substring (CAST(dbo.FiscalWeek('04',date) as varchar), 3,2)) +1 as Varchar) as Fiscal_year,

 

[r937]

you realize of course that a 4 4 5 scheme eventually has to have a 4 5 5 year, right?



r937.com | rudy.ca