If you want to consider Sunday as the first
day of the week, subtract the constant 7
(representing Sunday):
SELECT DATEPART(weekday,
GETDATE() + @@DATEFIRST - 7);
(Again, this calculation is independent of the
DATEFIRST setting.) So, to get all orders
placed on Tuesdays, you can use the following code (setting Monday as the logical
first day of the week):
SELECT OrderID, OrderDate,
CustomerID, EmployeeID
FROM dbo.Orders
WHERE DATEPART(weekday, OrderDate
+ @@DATEFIRST - 1) = 2;
This code is independent of any languagerelated settings that are in effect in the
session.
There's another elegant technique
for calculating a language-independent
weekday number. I learned this technique
from SQL Server MVP Steve Kass. Pick a
constant date whose weekday is what you
want to logically consider as the first day
of the week. Call this date anchor. For this
purpose, it's convenient to remember that the base date January 1, 1900—represented
by the integer 0—is a Monday. So, if you
want to consider Monday the first day of
the week, use the value 0; for Tuesday, use 1;
for Wednesday, use 2; and so on. Using the
DATEDIFF function, calculate the difference in terms of days between the anchor
date and the input date. Call that difference diff. The expression diff % 7 naturally will
return 0 if the input date has the same
weekday as the anchor date. More generally,
for an input date with a weekday number n (in respect to the logical date you chose first)
the expression diff % 7 will return n-1. So,
to get the weekday number n, you need to
use the expression diff % 7 + 1. Try it, using
the anchor 0 to make Monday the first day
of the week:
SET DATEFIRST 1;
(also try with 2, 3, 4, 5, 6, 7)
SELECT DATEDIFF(day, 0,
GETDATE()) % 7 + 1;
You'll always get the correct weekday
number for today's date, regardless of the
DATEFIRST setting.
To get all orders placed on Tuesdays,
considering Monday as the logical date first
setting, use the query
SELECT OrderID, OrderDate,
CustomerID, EmployeeID
FROM dbo.Orders
WHERE DATEDIFF(day, 0, OrderDate)
% 7 + 1 = 2;
If you're curious about which of the two
techniques to calculate a language-independent weekday number is faster, run the code
in Web Listing 1, http://www.sqlmag.com,
InstantDoc ID 94819. This code runs each
technique one million times. I got the results
in Table 1, which shows that the second
technique is faster than the first.
Always Simplifying
To save yourself grief, you must be able to
perform datetime-related calculations in a
manner that's independent of any language
settings. Always keep in mind that users of
your application might be running with
different language-related settings. Next
month, I'll discuss other datetime-related
calculations.
End of Article
Prev. page
1
2
[3]
next page -->
