Explain Codes LogoExplain Codes Logo

Get day of the week in SQL Server 2005/2008

sql
datefirst
datepart
sql-server
Alex KataevbyAlex Kataev·Sep 5, 2024
TLDR

You can grab the day of the week using SQL Server's DATEPART function paired with dw or weekday. If you need a custom start of the week, tweak SET DATEFIRST to your preferences. Here's a super clear example with Monday as the starting point:

SET DATEFIRST 1; -- Monday? Yes, it's the new Sunday now! SELECT DATEPART(dw, GETDATE()) AS DayOfWeek;

This code returns 1 for Monday and escalates up to 7 for Sunday, no matter how your server defines its week.

It's all about setting DATEFIRST

We'll be starting with DATEFIRST. Precisely, SQL Server's @@DATEFIRST sets the "first day of the week", which may vary depending on your cultural preferences or your business needs. So to check which day is SQL Server's week starter, use this: SELECT @@DATEFIRST AS StartOfWeek.

Running DATEPART(dw, GETDATE()) gives you the numerical representation of the current day of the week. But remember, the output depends on what you set as DATEFIRST.

In simpler words, to ensure consistency in your output, remember the DATEFIRST setting when using DATEPART().

Starting the week... your way

Not everyone's week starts on Sunday. Based on your requirements, your week may start on Monday (following ISO 8601). In that case, set DATEFIRST to 1. Here's how:

SET DATEFIRST 1; -- Welcome to the world where Monday is the new Sunday SELECT DATENAME(dw, GETDATE()) AS DayName, DATEPART(dw, GETDATE()) AS DayNumber;

Voila! You get the name of the day in DayName and its numbered equivalent in DayNumber.

Being flexible with DATEFIRST

Remember, if your DATEFIRST setting is different from the default (7), DATEPART outputs may seem a bit odd. If you've changed DATEFIRST, either reset it once you're done with your queries or make sure you're aware of these changes:

-- All things must be put back in their places SET DATEFIRST 7;

For more flexibility, you can use a CASE statement to map the numeric results from DATEPART to actual weekdays:

SELECT CASE DATEPART(dw, GETDATE()) WHEN 1 THEN 'Sunday' WHEN 2 THEN 'Monday' -- Continue with the remaining days END AS DayOfWeek;