SQL Week of Month without DATEFIRST

Question

I am working with dates so I have created a function that generates a SQL Table Calendar which returns Day, Month, WeekOfMonth, WeekOfYear and so on. Right now, for the Day of Month field I am using the following function:

    -- [WkNo]=Week number                                
    [WkNo]      = DATEPART(week,dt.DT),

But the problem is that when I run this on a SQL installed with Language = US English, the week setting is wrong cause the week starts from Sunday. I need to set the week starting from Monday, is it possible without the use of DATEPART?

Sorry, I misunderstood your question. See this answer from @t-clausen.dk: http://stackoverflow.com/a/13119920/2203084 — Felix Pamittan, Aug 10 '15 at 11:57

DhruvJoshi · Accepted Answer · 2015-08-10T11:37:15.977

Update: based on asker's comment that he cannot use the DATEFIRST approach I am updating answer.

Note:

This answer is generic in nature.

If instead of Monday you want the week to start from Tuesday, you can change the dateadd(dd,-1,dt.DT) to dateadd(dd,-2,dt.DT) and for Wednesday to dateadd(dd,-3,dt.DT).

Basically the formula becomes dateadd(dd,-n,dt.DT) for value of n ranging over 1(Monday) to 6 (Saturday).

SELECT  [WkNo]=
 ISNULL(DATEPART(week,
                      case 
                         when Year(dt.DT)>YEAR(dateadd(dd,-1,dt.DT)) 
                         then null  
                         else dateadd(dd,-1,dt.DT) 
                      end
                 ),1)

See working fiddle http://sqlfiddle.com/#!6/10a80/14

Old Answer: See MSDN documentation: https://msdn.microsoft.com/en-us/library/ms174420.aspx

When datepart is week (wk, ww) or weekday (dw), the return value depends on the value that is set by using SET DATEFIRST. January 1 of any year defines the starting number for the week datepart, for example: DATEPART (wk, 'Jan 1, xxxx') = 1, where xxxx is any year.

    SET DATEFIRST 1
 -- [WkNo]=Week number                                
    [WkNo]      = DATEPART(week,dt.DT),

Because I use a VIEW and a FUNCTION and I cannot use SET DATEFIRST 1 within their call and I cannot have sequential calls because the VIEW is used by a Report Engine. So within the VIEW or FUNCTION I should set DATEFIRST, which is not possible on SQL Server — Raffaeu, Aug 10 '15 at 09:35

score 0 · Answer 2 · answered Aug 10 '15 at 09:20

0

try

SET DATEFIRST {1,2,3,4,5,6,7(default, U.S. English)}

for your Query:

set datefirst 1
select [WkNo]= DATEPART(week,dt.DT)

See Here

answered Aug 10 '15 at 09:20

A_Sk

4,532
3
27
51

I cannot use DATEFIRST, so I cannot accept your solution – Raffaeu Aug 10 '15 at 09:29

SQL Week of Month without DATEFIRST

2 Answers2