| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
jec_blanco
Starting Member
1 Posts |
 Posted - 08/15/2007 : 05:02:36
|
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'. |
 |
|
|
nbison
Starting Member
1 Posts |
Posted - 06/07/2010 : 05:30:16
|
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 |
|
|
|
bsarlo
Starting Member
USA
3 Posts |
Posted - 07/22/2010 : 15:29:40
|
| 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? |
|
|
|
bsarlo
Starting Member
USA
3 Posts |
Posted - 07/22/2010 : 16:59:32
|
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?
|
|
|
|
bsarlo
Starting Member
USA
3 Posts |
Posted - 07/22/2010 : 19:02:38
|
| 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. |
|
|
|
vietnews
Starting Member
Vietnam
1 Posts |
Posted - 03/15/2012 : 23:37:19
|
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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 03/16/2012 : 01:29:31
|
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" |
|
|
|
allen12
Starting Member
3 Posts |
|
| |
Topic |
|
Article: Returning a week number for any given date and starting fiscal month
