| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2007-05-02 : 11:06:26
|
| 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 Post |
Posted - 2007-08-15 : 05:02:36
|
| Hi,When i copy and paste your code in my query analyzer, an error occursServer: Msg 170, Level 15, State 1, Procedure FiscalWeek, Line 11Line 11: Incorrect syntax near '@year'. |
 |
|
|
nbison
Starting Member
1 Post |
Posted - 2010-06-07 : 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, |
|
|
|
bsarlo
Starting Member
3 Posts |
Posted - 2010-07-22 : 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
3 Posts |
Posted - 2010-07-22 : 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
3 Posts |
Posted - 2010-07-22 : 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
1 Post |
Posted - 2012-03-15 : 23:37:19
|
| if you FiscalWeek('01', '12/31/2012')you only get 1 week !?so you should change your script tocreate 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) endelse 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 endset @weekNum = (@year*100)+((datediff(day, @firstweek, @myDate)/7)+1) return @weekNum end now it works fine. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2012-03-16 : 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 |
|
|
|
Article: Returning a week number for any given date and starting fiscal month