urgent -convert calendar period to fiscal period

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

urgent -convert calendar period to fiscal period

Author

Topic

chuotchich2000
Starting Member

7 Posts

Posted - 2008-10-11 : 21:29:00

I have a problem convert week number from calendar period to fiscal perioda such as

calendar period fiscal period YYWW
2008-09-29 2008-12-29 0853-> should be 0901

please help me...big thanks all of you
it's very urgent

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-12 : 02:32:50

try like this

SELECT DATENAME(yy,DATEADD(mm,3,'2008-09-29'))+DATENAME(wk,DATEADD(mm,3,'2008-09-29'))

chuotchich2000
Starting Member

7 Posts

Posted - 2008-10-12 : 10:10:59

quote:
Originally posted by visakh16

try like this

SELECT DATENAME(yy,DATEADD(mm,3,'2008-09-29'))+DATENAME(wk,DATEADD(mm,3,'2008-09-29'))

Thank you Visakh16 quick reply, it is work fine but I need to create a function convert from calendar year to fiscal year. my fiscal year end is usually dump into the last day Sunday in September (2008-09-29)...please advice what shall I need to do. big thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-12 : 10:15:03

so you need to get fiscal year end for each year? is that what you're asking for? if not, please specify what you're looking for giving some sample data & output.

chuotchich2000
Starting Member

7 Posts

Posted - 2008-10-12 : 10:33:06

quote:
Originally posted by visakh16

so you need to get fiscal year end for each year? is that what you're asking for? if not, please specify what you're looking for giving some sample data & output.

yes, it's correct ..so for focusing

chuotchich2000
Starting Member

7 Posts

Posted - 2008-10-12 : 10:37:13

here is my function but the output week number for calendar year. I am looking for week number for fiscal year.Please advise

CREATE Function [dbo].[MyWeekNo_A](@DateValue as DateTime) Returns Int
As
Begin
Declare @Date as Datetime
declare @Date2 as Datetime
Declare @Week as int
/******
-1 is Sunday
*******/
Select @Date = Convert(Varchar,Year(@DateValue)) + '-01-01', @Date2=DateAdd(DD,-1,@Date)

Select @Week = Case When WeekNo=0 Then dbo.MyWeekNo(@Date2) Else WeekNo End
From
(
Select
Case When DatePart(W,@Date) >=3 Then DatePart(WW,@DateValue) -1
Else DatePart(WW,@DateValue) End WeekNo
) as Weeks

Return @Week;
End

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-12 : 13:22:01

i'm not sure what you've given in function. why are comparing the week number of first day of year to >=3. can you explain?

chuotchich2000
Starting Member

7 Posts

Posted - 2008-10-12 : 23:40:24

quote:
Originally posted by visakh16

i'm not sure what you've given in function. why are comparing the week number of first day of year to >=3. can you explain?

actually i got this code from online. if you have any suggestion please advise

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-10-13 : 09:37:17

Fiscal year is something that varies from one organization to the next, so instead of posting code that you don’t really understand, just post the actual business rules that define what the fiscal year is.

CODO ERGO SUM

chuotchich2000
Starting Member

7 Posts

Posted - 2008-10-13 : 10:45:16

quote:
Originally posted by Michael Valentine Jones

Fiscal year is something that varies from one organization to the next, so instead of posting code that you don’t really understand, just post the actual business rules that define what the fiscal year is.

CODO ERGO SUM

i am new with SQL that's why i need help ...sorry and thanks for your tme

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 10:48:32

quote:
Originally posted by chuotchich2000

i am new with SQL that's why i need help ...sorry and thanks for your tme

but you need to specify what according to you represents a fiscal year.

chuotchich2000
Starting Member

7 Posts

Posted - 2008-10-13 : 11:26:04

but you need to specify what according to you represents a fiscal year.

[/quote]

as every year my fiscal week will be end on last the Sunday in Spetember of the year. the new fiscal year will start on Monday, here is fiscal period time i am looking for

calendar_period fiscal_period YYWW
2008-09-29 2008-12-29 0853-> should be 0901

from Calendar periods to fiscal period :
DATEADD(M,3,TRACKDATE] -> 2008-12-29 -> THIS SHOULD BE YYWW IS 0901 instead of 0853

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-13 : 11:43:05

quote:
Originally posted by chuotchich2000

but you need to specify what according to you represents a fiscal year.

as every year my fiscal week will be end on last the Sunday in Spetember of the year. the new fiscal year will start on Monday, here is fiscal period time i am looking for

calendar_period fiscal_period YYWW
2008-09-29 2008-12-29 0853-> should be 0901

from Calendar periods to fiscal period :
DATEADD(M,3,TRACKDATE] -> 2008-12-29 -> THIS SHOULD BE YYWW IS 0901 instead of 0853

[/quote]

CREATE FUNCTION [dbo].[ufn_YrWeek] (@Input datetime) RETURNS VARCHAR(50) as
Begin
declare @ret_value varchar(50)
declare @ret_value1 varchar(50)
select
@ret_value =
case
when
datename(dw,case when datename(month,@Input) in ('october','november','december') then convert(varchar,datename(year,@Input))+'1001' else convert(varchar,(datename(year,@Input)-1))+'1001' end)='monday'
then
case when datename(month,@Input) in ('october','november','december') then convert(varchar,datename(year,@Input))+'0929' else convert(varchar,(datename(year,@Input)-1))+'0929' end
when
datename(dw,case when datename(month,@Input) in ('october','november','december') then convert(varchar,datename(year,@Input))+'1001' else convert(varchar,(datename(year,@Input)-1))+'1001' end)='tuesday'
then
case when datename(month,@Input) in ('october','november','december') then convert(varchar,datename(year,@Input))+'0928' else convert(varchar,(datename(year,@Input)-1))+'0928' end
when
datename(dw,case when datename(month,@Input) in ('october','november','december') then convert(varchar,datename(year,@Input))+'1001' else convert(varchar,(datename(year,@Input)-1))+'1001' end)='wednesday'
then
case when datename(month,@Input) in ('october','november','december') then convert(varchar,datename(year,@Input))+'0927' else convert(varchar,(datename(year,@Input)-1))+'0927' end
when
datename(dw,case when datename(month,@Input) in ('october','november','december') then convert(varchar,datename(year,@Input))+'1001' else convert(varchar,(datename(year,@Input)-1))+'1001' end)='thursday'
then
case when datename(month,@Input) in ('october','november','december') then convert(varchar,datename(year,@Input))+'0926' else convert(varchar,(datename(year,@Input)-1))+'0926' end
when
datename(dw,case when datename(month,@Input) in ('october','november','december') then convert(varchar,datename(year,@Input))+'1001' else convert(varchar,(datename(year,@Input)-1))+'1001' end)='friday'
then
case when datename(month,@Input) in ('october','november','december') then convert(varchar,datename(year,@Input))+'0925' else convert(varchar,(datename(year,@Input)-1))+'0925' end
when
datename(dw,case when datename(month,@Input) in ('october','november','december') then convert(varchar,datename(year,@Input))+'1001' else convert(varchar,(datename(year,@Input)-1))+'1001' end)='saturday'
then
case when datename(month,@Input) in ('october','november','december') then convert(varchar,datename(year,@Input))+'1001' else convert(varchar,(datename(year,@Input)-1))+'1001' end
when
datename(dw,case when datename(month,@Input) in ('october','november','december') then convert(varchar,datename(year,@Input))+'1001' else convert(varchar,(datename(year,@Input)-1))+'1001' end)='sunday'
then
case when datename(month,@Input) in ('october','november','december') then convert(varchar,datename(year,@Input))+'0930' else convert(varchar,(datename(year,@Input)-1))+'0930' end
end
set @ret_value1=ceiling(convert(float,datediff(day,@ret_value,@Input))/7)

return case when datename(month,@Input) in ('october','november','december') then convert(varchar,(datename(year,@Input)+1))else convert(varchar,(datename(year,@Input))) end
+''+case when len(@ret_value1)=1 then '0'+@ret_value1 else @ret_value1 end

End

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-13 : 12:38:36

[code]
create function MyWeekNo(@DateValue as DateTime) returns varchar(6)
AS
BEGIN

declare @datevalue as datetime
set @datevalue = '2008-10-05'
declare @shiftdate as datetime
declare @beginfisc as datetime

select @beginfisc = cast(CONVERT(char(10), (dateadd(week,39,convert(varchar,year(@DateValue))+'-01-01')-(datepart(dw, dateadd(week,39,convert(varchar,year(@DateValue))+'-01-01')-1)-1)), 110) as datetime)

select @shiftdate = dateadd(week,datediff(week,@beginfisc,Convert(Varchar,Year(@DateValue)+1) + '-01-01'),@DateValue)

select
@datevalue, @beginfisc, @shiftdate,
cast(DATENAME(yy,@shiftdate) as varchar(4))+right('00'+rtrim(cast(DATENAME(wk,@shiftdate) as varchar(2))),2)
END
[/code]

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-13 : 12:50:25

quote:
Originally posted by hanbingl

[CODE]

create function MyWeekNo(@DateValue as DateTime) returns varchar(6)
AS
BEGIN

declare @shiftdate as datetime
declare @beginfisc as datetime

select @beginfisc = cast(CONVERT(char(10), (dateadd(week,39,convert(varchar,year(@DateValue))+'-01-01')-(datepart(dw, dateadd(week,39,convert(varchar,year(@DateValue))+'-01-01')-1)-1)), 110) as datetime)

select @shiftdate = dateadd(day,datediff(day,@beginfisc,Convert(Varchar,Year(@DateValue)+1) + '-01-01'),@DateValue)

return cast(DATENAME(yy,@shiftdate) as varchar(4))+right('00'+rtrim(cast(DATENAME(wk,@shiftdate) as varchar(2))),2)
END
[/CODE]

select dbo.MyWeekNo('20081018') will return you '200904' instead of '200903'.
select dbo.MyWeekNo('20080929') will return you '200901' instead of '200853'

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-13 : 12:56:07

He just said
2008-12-29 -> THIS SHOULD BE YYWW IS 0901 instead of 0853

By the way I modified my code, fixed the week shift problem. Now '20081018' is '200903'

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-13 : 12:58:53

HERE:


create function MyWeekNo(@DateValue as DateTime) returns varchar(6) 
AS
BEGIN

declare @shiftdate as datetime
declare @beginfisc as datetime

select @beginfisc = cast(CONVERT(char(10), (dateadd(week,39,convert(varchar,year(@DateValue))+'-01-01')-(datepart(dw, dateadd(week,39,convert(varchar,year(@DateValue))+'-01-01')-1)-1)), 110) as datetime)

select @shiftdate = dateadd(week,datediff(week,@beginfisc,Convert(Varchar,Year(@DateValue)+1) + '-01-01'),@DateValue)

return
cast(DATENAME(yy,@shiftdate) as varchar(4))+right('00'+rtrim(cast(DATENAME(wk,@shiftdate) as varchar(2))),2)
END

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-13 : 13:07:15

Nope, didn't work right :(

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-13 : 13:21:55

Okay, try this.
Week should shift correctly:


alter function MyWeekNo(@DateValue as DateTime) returns varchar(6) 
AS
BEGIN

declare @beginfisc as datetime
declare @return as varchar(6)
select @beginfisc = cast(CONVERT(char(10), (dateadd(week,39,convert(varchar,year(@DateValue))+'-01-01')-(datepart(dw, dateadd(week,39,convert(varchar,year(@DateValue))+'-01-01')-1)-1)), 110) as datetime)

if datediff(day,@beginfisc,@datevalue) >= 0
begin 
select @return= Convert(Varchar,Year(@DateValue)+1)+ right('00'+rtrim(cast( datename(wk,@datevalue)-39 as varchar(2))),2)
end
else if datediff(day,@beginfisc,@datevalue) = -1 --SUNDAY, so extra 1 day in week 53??  please clearify
begin
select @return= Convert(Varchar,Year(@DateValue))+ right('00'+rtrim(cast( datename(wk,@datevalue)+13 as varchar(2))),2)
end
else
begin
select @return= Convert(Varchar,Year(@DateValue))+ right('00'+rtrim(cast( datename(wk,@datevalue)+14 as varchar(2))),2)
end
return @return
end

Suresh1616
Starting Member

2 Posts

Posted - 2010-01-04 : 11:41:48

Can anyone get the reverse format i.e, from yyww to yyyymmdd and the date i expect to be the last day of the week specified i.e, a saturday.

For eg : yyww : 1002 (2010 2nd week)
expected result yyyy-mm-dd : 2010-01-09

Suresh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-05 : 03:14:05

quote:
Originally posted by Suresh1616

Can anyone get the reverse format i.e, from yyww to yyyymmdd and the date i expect to be the last day of the week specified i.e, a saturday.

For eg : yyww : 1002 (2010 2nd week)
expected result yyyy-mm-dd : 2010-01-09

Suresh

how will you determine if 10 represents 2010 or 1910? whats base date in your case?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources