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.
| Author |
Topic |
|
Portucale
Starting Member
2 Posts |
 Posted - 2010-02-23 : 07:57:11
|
| Hi,Is there any formula which I could ascertain the Week Number according to the ISO (8601) meaning that the first week of the year is determined by "the week with the year's first Thursday in it (the formal ISO definition)", as for example in 2009 there was 53 weeks being week 53 from Dec, 28th 2009 to Jan, 3rd 2010.Many Thanks for your help.Regs, |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2010-02-23 : 11:01:44
|
| CREATE FUNCTION [dbo].[WeekNum_ISO] ( @Date DATETIME)RETURNS INT AS BEGIN DECLARE @Result INT, @i INT, @Year INT SET @i = (@@DATEFIRST + DATEPART(Weekday,@Date) - 2) % 7 SET @Year = DATEPART(Year,DATEADD(Day,3 - @i,@Date)) SET @Result = 100 * @Year + (DATEDIFF(Day,CONVERT(CHAR(4),@Year) + '0101',@Date) - @i + 10) / 7 RETURN @ResultEND |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-02-23 : 11:56:06
|
| ISO Week of Year Functionhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60510ISO Year Week Day of Week Functionhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60515Function that loads a date table with various ISO week columns:Date Table Function F_TABLE_DATEhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519ISO_YEAR_WEEK_NO ISO 8601 year and week in format YYYYWW, Example = 200403ISO_WEEK_NO ISO 8601 week of year in format WW, Example = 52ISO_DAY_OF_WEEK ISO 8601 Day of week number, Mon=1, Tue=2, Wed=3, Thu=4, Fri=5, Sat=6, Sun=7ISO_YEAR_WEEK_NAME ISO 8601 year and week in format YYYY-WNN, Example = 2004-W52ISO_YEAR_WEEK_DAY_OF_WEEK_NAME ISO 8601 year, week, and day of week in format YYYY-WNN-D, Example = 2004-W52-2CODO ERGO SUM |
|
|
|
Portucale
Starting Member
2 Posts |
Posted - 2010-03-04 : 02:14:26
|
| Many Thanks for your help. |
|
|
|
|
|
|
|
|
WeekNumber ISO