yearweek on SQL server

Question

How can I extract the year and week combination from a date in SQL Server using T-SQL and have it match the MySQL yearweek result?

For example these MySQL queries (Click here for MySQL SQL Fiddle):

SELECT yearweek('2013-12-31');
SELECT yearweek('2014-01-01');

This returns 201352 for both dates. That is the expected result.

But in SQL Server...

• datepart works as expected for the year extract
• sometimes datepart does not return the expected value for iso_week

The MySQL result cannot be achieved with this T-SQL query...

SELECT datepart(year, @dt) * 100 + datepart (iso_week, @dt);

T-SQL versions of the MySQL queries above (Click here for T-SQL SQL Fiddle):

SELECT datepart(year, '2013-12-31') * 100 + datepart (iso_week, '2013-12-31');
SELECT datepart(year, '2014-01-01') * 100 + datepart (iso_week, '2014-01-01');

The result is 201352 for the first date and 201401 for the second date.

However, this is not the expected result because...

• 2014-01-01 belongs to the last week of 2013
• So the expected result is 201352

Do any of you more experienced T-SQL developers know how to extract the year/week of a given date and have this match what I see in MySQL?

I need to have the week start on Monday. This is why I am using iso_week. I have tested the results with week anyway and found the same issue. This query also produces 201401 when 201352 is expected.

SELECT datepart(year, '2014-01-01') * 100 + datepart (week, '2014-01-01');

Solution

If you look at the ISO_Week datepart definition at http://msdn.microsoft.com/en-us/library/ms174420.aspx, you'll see the following:

ISO 8601 includes the ISO week-date system, a numbering system for weeks. Each week is associated with the year in which Thursday occurs...

The numbering system in different countries/regions might not comply with the ISO standard.

Since January 1, 2014 was a Wednesday; January 2, 2014 was the first Thursday of the year and thus week 1 of 2014 (according to ISO 8601).

Furthermore, looking at the MySQL definitions for yearweek and week, there are several mode options (http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_week)

So, I think you're going to have to write your own yearweek function for the week counting rule you want:

CREATE FUNCTION dbo.yearweek(@date date)
RETURNS INT
as
 begin
    set @date = dateadd(dd,-datepart(dw,@date)+1, @date)

    return datepart(year,@date)*100 + datepart(week,@date)
 end
go

select dbo.yearweek('2013-12-31'), dbo.yearweek('2014-01-01')

NOTE: I haven't fully tested this code, and I'm not sure exactly what your requirements are. This is just meant as an example of the type of process you need to follow.

Comments

[SQL.injection]

how exactly "SELECT YEAR('2014-01-01') AS [YEAR] , DATEPART(WEEK, '2014-01-01')" is supposed to look like 2013-52? I really don't understand ... because the the 1st of January of 2014 belongs to the last week of 2013...

[SQL.injection]

there is no typo on the iso week, I want the week to start on Monday, therefore I use ISO_WEEK.

[M.Ali]

I have selected both values separately for demo purpose , if you want me to concatenate it as well see my update now.

[SQL.injection]

did you tried your query? don't you understand the problem is that 2014-01-01 is week 52 of 2013?

[Jon Jaussi]

@SQL.injection testing a few different dates with @chezy525's function. What would you expect for this result? SELECT '2012-01-02' as MyDate, dbo.yearweek('2012-01-02') as yearweek_result. I see 201153. I think you would want 201201. SELECT '2014-01-01' as MyDate, dbo.yearweek('2014-01-01') as yearweek_result does give the expected result 201352

[Rich]

didn't say there was a typo, just that you might want a different parameter. read the description.