![]() The week number expression is a complex calculation and I won’t bother to explain it here, but it works! The assumption for this solution is that if a week starts on the Monday and if a new month starts during the week the new month dates will be treated as being in the last week of the previous month. Once again we have used nested functions to achieve our goal. (6 + DAY(OrderDate + 1 - DATEPART(WEEKDAY,(OrderDate-1))))/7 AS 'Week Number Of Order''s Month' ![]() DAY(OrderDate + 1 - DATEPART(WEEKDAY,(OrderDate-1)))/7 AS 'Day Of Month Tomorrow - Week day yesterday', DAY(OrderDate + 1 - DATEPART(WEEKDAY,(OrderDate-1))) AS 'Day Of Month Tomorrow - Week day yesterday', DAY(OrderDate + 1) AS 'Day of Month Tomorrow', DATENAME(WEEKDAY,(OrderDate-1)) AS 'Weekday Name Yesterday', DATEPART(WEEKDAY,(OrderDate-1)) AS 'Weekday Yesterday', DATEPART(WEEKDAY,OrderDate) AS 'Weekday', So now for our second question! Question 2: How do I retrieve the week number within a month, where a week starts on a Monday? /*ĭisplay the week number for an order's month (where a week starts on a Monday)ĭATENAME(WEEKDAY,OrderDate) AS 'Weekday Name', The following example shows a comparison of DATENAME and DATEPART: SELECT DATEPART(WEEKDAY, GETDATE()), DATENAME(WEEKDAY, GETDATE()),ĭATEPART(MONTH, GETDATE()), DATENAME(MONTH, GETDATE()) (weekdays start with 1 for Sunday by default, but this can be changed). DATEPART FunctionĭATEPART works in the same way as DATENAME, but returns numbers for the weekday and month instead of the word. In Part 2 of our “ SQL Server - Working With Dates” blog we use the DATEPART function.
0 Comments
Leave a Reply. |