Get day of the week in SQL Server 2005/2008
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:
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:
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:
For more flexibility, you can use a CASE
statement to map the numeric results from DATEPART
to actual weekdays:
Was this article helpful?