Sql DATEPART(dw,date): Setting Monday as 1 and Sunday as 7

Handling Custom Weekdays

In unique calendar scenarios or situations that don't allow for changes to session-level settings, you can rely on a hardy formula for determining the day of the week. The formula below doesn't depend on SET DATEFIRST, but will always give you Monday as 1 and Sunday as 7:

SELECT ((DATEPART(dw, date) + @@DATEFIRST + 5) % 7 + 1) AS DayOfWeek


Restoring Default Week Start

To reset DATEFIRST to its default value post-adjustment, especially beneficial in shared environments or connection pooling, use SET DATEFIRST 7. As long as you know the difference between Sunday and Monday, you're good, right?

SET DATEFIRST 7; --Not all who wander are lost, some are just looking for Sunday


Weekday Numbers in Different Settings

In different regions and systems, the start of the week can vary:

• In some Middle Eastern countries, the week begins on Saturday.
• For certain international standards like ISO 8601, the week starts on Monday.

For applications like this, our rugged formula works effectively without DATEFIRST. It offers consistency, irrespective of server settings.

Language-Aware Weekday Calculations

If your application displays weekdays, consider a DATENAME function-based solution:

--The Joy of Naming Days: SQL Edition
SELECT CASE DATENAME(weekday, date)
    WHEN 'Monday' THEN 1
    WHEN 'Tuesday' THEN 2
    WHEN 'Wednesday' THEN 3
    WHEN 'Thursday' THEN 4
    WHEN 'Friday' THEN 5
    WHEN 'Saturday' THEN 6
    WHEN 'Sunday' THEN 7
END AS DayOfWeek


And remember, kiddos, SQL Server language settings might affect DATENAME() results.

Your Route to Efficiency

Balancing accuracy and efficiency is key. By dodging a CASE statement with SET DATEFIRST, you can work faster than a speedy sloth. Our hardy formula ensures correct weekday numbering across various configurations, serving up a nifty combo of customization and performance.