SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Returning a week number for any given date and starting fiscal month
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 05/02/2007 :  11:06:26  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
This article was written by Paul Alcon. Paul writes "Sql Server comes with a host of built in functions such as ISNULL, CONVERT and CAST. Now if that wasn't enough rope to hang ourselves with, as of Sql Server 2000 we gained the ability to create our own user defined functions. In this article I will be looking at the three main date functions DATEADD, DATEPART and DATEDIFF (there is a fourth called DATENAME but I want to get to the end of this article before you fall asleep so I decided to leave it for another date and time! And no it doesn't foretell the name of your future blind date so it's not as interesting as it sounds anyway) Then I will be combining all three in a user defined function of our own by which time our necks will be well and truly stretched!"

Article Link.

jec_blanco
Starting Member

1 Posts

Posted - 08/15/2007 :  05:02:36  Show Profile  Reply with Quote
Hi,

When i copy and paste your code in my query analyzer, an error occurs

Server: Msg 170, Level 15, State 1, Procedure FiscalWeek, Line 11
Line 11: Incorrect syntax near '@year'.
Go to Top of Page

nbison
Starting Member

1 Posts

Posted - 06/07/2010 :  05:30:16  Show Profile  Reply with Quote
Hi there,

Not sure whether this topic is still on the radar but will try my luck still.

Firs of all, many thanks for this function. It has helped me alot, as I am very new to MS SQL Sever.

I have come across a tiny issue when SET DATEFIRST = 1 (Monday). It does not return the first Monday of the given month as week 1.

For example, the first Monday in April 2010 is 5th April. But I keep getting 29 March 2010 as Week 1 and 5th April as week 2.

Please note this problem only appears to be happening with set datefirst = 1. any other week start dates the function works correctly.

I think there could be an issue on my end?

Many thanks,


Edited by - nbison on 06/07/2010 05:31:34
Go to Top of Page

bsarlo
Starting Member

USA
3 Posts

Posted - 07/22/2010 :  15:29:40  Show Profile  Reply with Quote
Week 1 for a FY starting in April on Monday would start on 5/29 since 4 days of the first week fall in April. Is that right?
Go to Top of Page

bsarlo
Starting Member

USA
3 Posts

Posted - 07/22/2010 :  16:59:32  Show Profile  Reply with Quote
I tested your code and it worked great for returning the week number. How would I return the date of the last day of the returned week number?
Go to Top of Page

bsarlo
Starting Member

USA
3 Posts

Posted - 07/22/2010 :  19:02:38  Show Profile  Reply with Quote
Sorry for the triple post but I don't see an edit button. Your code fails for 2011 with a starting month of July. The fourth of July 2011 is actually in the second week, not the first.
Go to Top of Page

vietnews
Starting Member

Vietnam
1 Posts

Posted - 03/15/2012 :  23:37:19  Show Profile  Reply with Quote
if you FiscalWeek('01', '12/31/2012')
you only get 1 week !?

so you should change your script to

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+'04', 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+'04', 102)
-- set @firstWeek = dateadd(day, (1-datepart(dw, @firstWeek)), @firstWeek)
-- end


if datepart(year, @myDate) = datepart(year, @firstWeek)
begin
set @firstWeek = DATEADD(yy,DATEDIFF(yy,0,@firstWeek),0)
set @year = datepart(year, @myDate)
end
else
begin
while @myDate < @firstWeek --Repeat the above steps but for previous year
begin
set @year = @year - 1
set @firstWeek = convert(datetime, str(@year)+@startMonth+'04', 102)
set @firstWeek = dateadd(day, (1-datepart(dw, @firstWeek)), @firstWeek)
end
end


set @weekNum = (@year*100)+((datediff(day, @firstweek, @myDate)/7)+1)
return @weekNum
end


now it works fine.

Edited by - vietnews on 03/16/2012 00:03:33
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 03/16/2012 :  01:29:31  Show Profile  Visit SwePeso's Homepage  Reply with Quote
If you are using SQL Server 2008, just use the ISO_WEEK option for DATEPART function.



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

allen12
Starting Member

3 Posts

Posted - 03/30/2012 :  06:36:21  Show Profile  Reply with Quote
Really a good article.

Best web hosting | Best hosting service

Edited by - allen12 on 03/30/2012 07:53:29
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000