CODEWITHSUNDEEP
sql-servert-sqldatesql-server-2012
Serdia
Serdia

Reputation: 4428

How to get "Week of 2nd" based on date field in SQL Server 2012

I have a DueDate column in my table and based on that date, I want to display the "Week of ..." value based on Monday date.

So I want something like this:

enter image description here

Upvotes: 1

Views: 96

Answers (3)

Jessica
Jessica

Reputation: 36

I'm thinking you can use the DATEPART function to get the numeric day of the week, then build in a case statement to account for the Monday start... like this (where you'd substitute your date where I have '10/1/2017'):

select 'Week of ' + CONVERT(VARCHAR(10),DATEADD(dd, -DATEPART(dw,'10/1/2017')
   + CASE WHEN DATEPART(dw,'10/1/2017') = 1 THEN -5 ELSE 2 END, '10/1/2017'),101)

Edit: just realized my answer is effectively the same as Psidom's; yay slow typing.

Upvotes: 1

akuiper
akuiper

Reputation: 214967

Try this one:

select date,
('week of ' + 
 convert(varchar(2), datepart(day, DATEADD(day, -(case when datepart(weekday, date)=1 then 8 else datepart(weekday, date) end)+2, date))))
as monday
from table1;

See Fiddle.

This could be simpler if sunday is not considered the same as the last monday but the next

Upvotes: 1

TheMountainPass
TheMountainPass

Reputation: 51

You can combine the answer found here with the "DatePart" function like so:

SELECT  
        'Week Of ' + CONVERT(VARCHAR(2), DATEPART(DAY,DATEADD(DAY, (2-DATEPART(WEEKDAY, GETDATE())), GETDATE()) )) + CASE
                                                                      WHEN DATEPART(DAY, GETDATE()) % 100 IN (11,12,13) THEN 'th' --first checks for exception
                                                                      WHEN DATEPART(DAY, GETDATE()) % 10 = 1 THEN 'st'
                                                                      WHEN DATEPART(DAY, GETDATE()) % 10 = 2 THEN 'nd'                                                                                                              
                                                                      WHEN DATEPART(DAY, GETDATE()) % 10 = 3 THEN 'rd'                                                                                                              
                                                                      ELSE 'th' --works for num % 10 IN (4,5,6,7,8,9,0)                                                                                                         
                                                                    END

Upvotes: 2

Related Questions