Rob Farley

Rob Rob Farley has been consulting in IT since completing a Computer Science degree with first class honours in 1997. Before moving to Adelaide, he worked in consultancies in Melbourne and London. He runs the development department in one of Australia's leading IT firms, as well as doing database application consultancy and training. He heads up the Adelaide SQL Server User Group, and holds several Microsoft certifications.

Rob has been involved with Microsoft technologies for most of his career, but has also done significant work with Oracle and Unix systems. His preferred database is SQL Server and his preferred language is C#. Recently he has been involved with Microsoft Learning in the US, creating and reviewing new content for the next generation of Microsoft exams.

Over the years, Rob's clients have included BP Oil, OneLink Transit, Accenture, Avanade, Australian Electorial Commission, the Chartered Institute of Personnel and Development, the Royal Borough of Kingston, Help The Aged, Unisys, Department of Treasury and Finance (Vic), National Mutual, the Bible Society and others.

Did you mean to come here? My blog is now at http://msmvps.com/blogs/robfarley



07 September 2006

@@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-wide config values or something. Grab @fortnightstart from there before you need it, or @paymonthoffset, or whatever.

You can also find this at: http://sqlblogcasts.com/blogs/rob_farley/articles/999.aspx

Posted by Rob Farley at 11:29 am