I am running SQL Server 2008 on a machine with regional settings that have Monday as the first day of the week. If I create a computed column in a table to compute the day of week for a date field then I get 2 for a Monday date instead of 1.
Is there some property for the table or database or server that I need to set ?
The first day of the week is based on your language settings of the server. The default setting for us_english is 7 (Sunday)
You can find the current first day of the week by using SELECT @@DATEFIRST
However, you can use DATEFIRST
for this. Just put it at the top of your query
SET DATEFIRST 1;
this sets Monday to the first day of the week for the current connection.
http://technet.microsoft.com/en-us/library/ms181598.aspx
You can use DATEPART(dw, GETDATE())
but be aware that the result will rely on SQL server setting @@DATEFIRST
value which is the first day of week setting (In Europe default value 7 which is Sunday).
Alternative way is to explicitly specify the first day of week value as parameter and avoid depending on @@DATEFIRST
setting. You can use the following formula to achieve that when need it:
(DATEPART(dw, GETDATE()) + @@DATEFIRST + 6 - @WeekStartDay) % 7 + 1
where @WeekStartDay
is the first day of the week you want for your system (from 1 to 7 which means from Monday to Sunday).
I have wrapped it into below function so we can reuse it easily:
CREATE FUNCTION [dbo].[GetDayInWeek](@InputDateTime DATETIME, @WeekStartDay INT)
RETURNS INT
AS
BEGIN
--Note: @WeekStartDay is number from [1 - 7] which is from Monday to Sunday
RETURN (DATEPART(dw, @InputDateTime) + @@DATEFIRST + 6 - @WeekStartDay) % 7 + 1
END
Example usage:
GetDayInWeek('2019-02-04 00:00:00', 1)
It is equivalent to following (but independent to DATEFIRST setting):
SET DATEFIRST 1
DATEPART(dw, '2019-02-04 00:00:00')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With