Yearweek On Sql Server

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

Solution 1:

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:

CREATEFUNCTION dbo.yearweek(@datedate)
RETURNSINTasbeginset@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.

Solution 2:

I think it's because you're using "ISO_WEEK" as the datepart

Look at the description under the "ISO_WEEK datepart" here: http://msdn.microsoft.com/en-us/library/ms174420.aspx

using "WEEK" instead might get the desired result

Solution 3:

TO extract YEAR, MONTH or DAY you can simply use the YEAR, MONTH and DAY function as shown in the followinf example. for any other part you have to use the DATEPART function , read here for a detailed list of arguements you can pass to DATEPART function.

SELECT YEAR(GETDATE()) AS [YEAR]
       , DATEPART(WEEK, GETDATE()) [Week]

UPDATE

SELECTCAST(YEAR(GETDATE()) ASVARCHAR(4))  +'-'+CAST(DATEPART(WEEK, GETDATE()) ASVARCHAR(2)) 

Solution 4:

It seems to me that what you want is a week number based on the previous Monday. Is this correct? If so, iso_week is no use to you as it is based on this week's Thursday.

To get the year and week based on the previous Monday, I think you need something like the following.

set datefirst 1 ;

select
    SomeDate ,
    datename( weekday, SomeDate ) as [WeekdayName] , 
    datepart( weekday, SomeDate ) as [Weekday] , 
    datepart( week, SomeDate ) as [Week] ,
    datepart( iso_week, SomeDate ) as [ISO_week] ,
    dateadd( day, -( datepart( weekday, SomeDate ) - 1 ), SomeDate ) as [PreviousMonday] ,
    datepart( year, dateadd( day, -( datepart( weekday, SomeDate ) - 1 ), SomeDate ) ) as [MondayYear] ,
    datepart( week, dateadd( day, -( datepart( weekday, SomeDate ) - 1 ), SomeDate ) ) as [MondayWeek] 
from 
    dbo.DateDemoorder by
    SomeDate ;

Abbreviated sample output is as follows.

SomeDatePreviousMondayMondayYearMondayWeek2013-12-25  2013-12-23  2013    522013-12-26  2013-12-23  2013    522013-12-27  2013-12-23  2013    522013-12-28  2013-12-23  2013    522013-12-29  2013-12-23  2013    522013-12-30  2013-12-30  2013    532013-12-31  2013-12-30  2013    532014-01-01  2013-12-30  2013    532014-01-02  2013-12-30  2013    532014-01-03  2013-12-30  2013    532014-01-04  2013-12-30  2013    532014-01-05  2013-12-30  2013    532014-01-06  2014-01-06  2014    2