Blog Post
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:
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?
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.
This is then subtracted from the next day’s date to create the date of of the month
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.
Share This Post
Mandy Doward
Managing Director
PTR’s owner and Managing Director is a Microsoft MCSE certified Business Intelligence (BI) Consultant, with over 30 years of experience working with data analytics and BI.
Frequently Asked Questions
Couldn’t find the answer you were looking for? Feel free to reach out to us! Our team of experts is here to help.
Contact Us