Re: ISO 8601 Week in SQL Query
Andrea Montanari
Eli wrote:
> Hello.
> How can I receive the ISO 8601 week format retrevied from a Date
> object of SQL?
>
> I now use :
> DATEADD(d, -1, DATEADD(ww, DATEDIFF(ww,0,stamptime), 0))
> wo get the week of the year, by this returns the first week of the
> year as where 1 of the month is, also each week starts with sunday
> and not mondy.
>
> How can I customize is to fit ISO 8601?
if I understood your question, from Joe Celko's "Data & Databases: concepts
in practice"
"...
Week Format
Although not as common in the United States as it is in Europe, many
commercial and
industrial applications use the week within a year as a unit of time. Week
01 of a year is
defined as the first week in that year that has a Thursday, which is
equivalent to the week
that contains the fourth day of January.
In other words, the first week of a new year is the week that has the
majority of its days in
the new year. Week 01 might also contain days from the previous year, and
the week
before week 01 of a year is the last week (52 or 53) of the previous year
even if it
contains days from the new year. The days of the week are numbered from 1
(Monday)
through 7 (Sunday).
The standard notation uses the letter W to announce that the following two
digits are a
week number. The week number component of the vector can be separated with a
hyphen or not, as required by space: 1999-W01 or 1999W01.
This notation can be extended by a single digit between 1 and 7 for the day
of the week.
For example, the day 1996-12-31, which is the Tuesday (day 2) of the first
week of 1997,
can be shown as 1997-W01-2 or 1997W012.
The ISO standard avoids explicitly stating the possible range of week
numbers, but a little
thought will show that the range is between 01 to 52 or between 01 to 53,
depending on
the particular year. There is one exception to the rule that a year has at
least 52 weeks: the
year 1753, when the Gregorian calendar was introduced, had less than 365
days and
therefore less than 52 weeks.
..."
that way you can adjust your computation, perhaps something like
SET NOCOUNT ON
USE tempdb
GO
CREATE FUNCTION dbo.ISO8601Week (
@d datetime
) RETURNS int
AS BEGIN
DECLARE @ISOoffset int
SET @ISOoffset = 0
IF DATEPART( dd, (DATEPART(yyyy, @d ) + '0101') )> 7 SET @ISOoffset = -1
RETURN ( SELECT DATEPART(wk, @d) + @ISOoffset )
END
GO
DECLARE @curFMT int
IF @curFMT <> 7 SET DATEFIRST 7 -- Set Sunday as 1st week day
PRINT '1999-01-01 = Friday'
SELECT DATEPART(wk,'19990101') AS [Week SQL], dbo.ISO8601Week ('19990101')
AS [Week ISO]
SELECT DATEPART(wk,'19991231') AS [Week SQL], dbo.ISO8601Week ('19991231')
AS [Week ISO]
PRINT '2005-01-01 = Wednesday'
SELECT DATEPART(wk,'20050101') AS [Week SQL], dbo.ISO8601Week ('20050101')
AS [Week ISO]
SELECT DATEPART(wk, GETDATE()) AS [Week SQL], dbo.ISO8601Week (GETDATE())
AS [Week ISO]
PRINT '2006-01-01 = Saturday'
SELECT DATEPART(wk, '20060101') AS [Week SQL], dbo.ISO8601Week ('20060101')
AS [Week ISO]
PRINT '1998-01-01 = Thursday'
SELECT DATEPART(wk, '19980101') AS [Week SQL], dbo.ISO8601Week ('19980101')
AS [Week ISO]
IF @curFMT <> @@DATEFIRST SET DATEFIRST @curFMT
GO
DROP FUNCTION dbo.ISO8601Week
--<---------------------
1999-01-01 = Friday
Week SQL Week ISO
----------- -----------
1 1
Week SQL Week ISO
----------- -----------
53 53
2005-01-01 = Wednesday
Week SQL Week ISO
----------- -----------
1 0
Week SQL Week ISO
----------- -----------
52 51
2006-01-01 = Saturday
Week SQL Week ISO
----------- -----------
1 0
1998-01-01 = Thursday
Week SQL Week ISO
----------- -----------
1 1
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply
Hugo Kornelis
>Hello.
>How can I receive the ISO 8601 week format retrevied from a Date object of
>SQL?
>
>I now use :
>DATEADD(d, -1, DATEADD(ww, DATEDIFF(ww,0,stamptime), 0))
>wo get the week of the year, by this returns the first week of the year as
>where 1 of the month is, also each week starts with sunday and not mondy.
>
>How can I customize is to fit ISO 8601?
Hi Eli,
Check Books Online, subject CREATE FUNCTION Example A.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)