@@datefirst

@@datefirst tells you what SQL says the first day of the week is (see the linked MSDN page for more). 1=Monday, 7=Sunday. The default for US English is 7. I guess the people who decide when movies come out have it set to 4 or 5.

But, should you let the start of the week be determined by @@datefirst, so that if policy on the Sunday/Monday argument changes you can just alter @@datefirst and let it be done?

Or should you make your code enforce the start of the week as per the policy, regardless of what @@datefirst is set to, with code such as:

declare @df_orig int
select @df_orig = @@datefirst --This is so we can reset it at the end
declare @df int
set @df = 1
while (@df <= 7)
begin
    set datefirst @df
    select
        @@datefirst as df,
        datepart(dw,'20060907') as dw,
        (@@datefirst + datepart(dw,'20060907')) % 7 as fixed
    set @df = @df + 1
end
set datefirst @df_orig --Resetting it

So, (@@datefirst + datepart(dw,'20060907')) % 7 will always give 5.

If I want to adjust a date so that I count the number of weeks based on a Thursday start, I can do this with:

datediff(week, '19900101',dateadd(day, @@datefirst - 4, '20060907'))

(where '19900101' is an arbitrary date - if you want to start counting at one somewhere else, then be my guest. The day of the week of this date is not important - it just marks the week in which our 'weeknumber' will be zero)

Why is this useful?

For example - my typical week starts on Sunday. I get frustrated by calendars that start on Mondays, it always throws out what day I think things are. If only I could change the calendars that my kids' school sends us...

But pay-wise, the fortnight starts on a Monday. So I have a sample fortnight start in the system, and I use datediff(day,@fortnightstart,@d)/14 to determine the fortnight that something fits into.

For people paid by the month, the month actually starts on the 25th of each month. So for that, I subtract the offset and strip the day off.

So for any code that I write, I want to know what fixes the start of the week/month. If it's a company-wide policy, then @@datefirst could make sense (and it makes calculations easier). If it's fixed by something else, then it's worth making sure that you have this defined somewhere, and use that. Like in a table of system-wideconfig values or something. Grab @fortnightstart from there before you need it, or @paymonthoffset, or whatever.
Published Thursday, September 7, 2006 3:52 AM by Rob Farley

Comments

# Checking date functions

Thursday, September 7, 2006 4:15 AM by Rob Farley
Seems whenever I write a function that affects dates, I want to make sure it works. I wrote an article...

# A couple of short datetime articles

Thursday, September 7, 2006 4:27 AM by Rob Farley
One about @@datefirst at http://sqlblogcasts.com/blogs/rob_farley/articles/999.aspx and one about testing...