SQL Server - Working With Dates Part 2

In Part 2 of our “SQL Server - Working With Dates” blog we use the DATEPART function.

DATEPART Function

DATEPART works in the same way as DATENAME, but returns numbers for the weekday and month instead of the word. (weekdays start with 1 for Sunday by default, but this can be changed).

The following example shows a comparison of DATENAME and DATEPART:

SELECT DATEPART(WEEKDAY, GETDATE()), DATENAME(WEEKDAY, GETDATE()),
	   DATEPART(MONTH, GETDATE()), DATENAME(MONTH, GETDATE())

And here are the results:

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?

/*
	Display the week number for an order's month (where a week starts on a Monday)
*/
USE AdventureWorks2014
GO
SELECT DISTINCT OrderDate, 
		DATENAME(WEEKDAY,OrderDate) AS 'Weekday Name',
		--DATEPART(WEEKDAY,OrderDate) AS 'Weekday',
		--DATEPART(WEEKDAY,(OrderDate-1)) AS 'Weekday Yesterday',
		--DATENAME(WEEKDAY,(OrderDate-1)) AS 'Weekday Name Yesterday',
		--DAY(OrderDate) AS 'Day Of Month Today',
		--DAY(OrderDate + 1) AS 'Day of Month Tomorrow',
		--DAY(OrderDate + 1 - DATEPART(WEEKDAY,(OrderDate-1))) AS 'Day Of Month Tomorrow - Week day yesterday',
		--DAY(OrderDate + 1 - DATEPART(WEEKDAY,(OrderDate-1)))/7 AS 'Day Of Month Tomorrow - Week day yesterday',
		(6 + DAY(OrderDate + 1 - DATEPART(WEEKDAY,(OrderDate-1))))/7  AS 'Week Number Of Order''s Month'
FROM Sales.SalesOrderHeader
ORDER BY OrderDate

Once again we have used nested functions to achieve our goal.

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.

• This can be seen from the screenshot – 01/06/2011 is shown as being in week 5 rather than week 1 because the start of that week was in May.

(6 + DAY(OrderDate + 1 - DATEPART(WEEKDAY,(OrderDate-1))))/7  AS 'Week Number Of Order''s Month'

DATEPART is used to extract the weekday number of the previous day (you can see this expression commented out in the full script.

		DATENAME(WEEKDAY,(OrderDate-1)) AS 'Weekday Name Yesterday',

This is then subtracted from the next day’s date to create the date of  of the month

        OrderDate + 1 - DATENAME(WEEKDAY,(OrderDate-1)) 

The DAY function is then used to extract the day of the day of the month of the newly created date.

Finally 6 is added to the derived day of the month and divided by 7. Integer arithmetic ensures that the integer part of the division is returned representing the week number of the month.

If you would like to learn more about SQL Server date functions and SELECT statements take a look at our SQL Server Training Courses.