In previous calculations of "last" values
(e.g., last day of the month), you simply
used the minus operator to subtract 1 day.
There was no need to use the DATEADD
function to subtract a day because day is
the default unit assumed with the use of a
plus or minus operator to add or subtract
an integer from a datetime value. Here, you
need to subtract a minute from the next
hour, so you use the DATEADD function
for this purpose.
Language-Independent
Weekday
Another common need in datetime calculations is to calculate the weekday of a given
datetime value. You would need this calculation, for example, to return all orders placed
on a Tuesday. It sounds like a simple task for
the DATENAME function, as follows:
USE Northwind;
SELECT OrderID, OrderDate,
CustomerID, EmployeeID
FROM dbo.Orders
WHERE DATENAME(weekday,
OrderDate) = ‘Tuesday';
However, this code will work correctly only
if the effective language setting of the session
running the code is English. Try running
this code in a session in which the effective
language is, for example, Italian, and you'll
get back an empty set
SET Language Italian;
SELECT OrderID, OrderDate,
CustomerID, EmployeeID
FROM dbo.Orders
WHERE DATENAME(weekday,
OrderDate) = ‘Tuesday';
If you want your application to serve inter-
national users, you'll want your code to work
correctly regardless of the effective language
setting. In other words, you want your
expressions to be language-independent.
One option you might consider is
to use the DATEPART function, specifying the weekday part, which returns
a weekday number. The expression
DATEPART(weekday, OrderDate) returns
the weekday number. However, the
weekday number is dependent on the
session's DATEFIRST (first day of the
week) setting, which in turn is dependent
on the login's language setting. For example, if the login's language setting is us_english,
the DATEFIRST setting is implicitly set
to 7 (which means Sunday is the first day
of the week). So, in a session in which the
effective language is us_english, you'll get 3
for a Tuesday. Your query for orders placed
on Tuesdays would look like
SELECT OrderID, OrderDate,
CustomerID, EmployeeID
FROM dbo.Orders
WHERE DATEPART(weekday,
OrderDate) = 3;
If the login's language is Italian, the DATEFIRST setting is implicitly set to 1 (which
means Monday is the first day of the week),
so the expression DATEPART(weekday,
OrderDate) will return 2 for a Tuesday.
Therefore, to get all orders placed on
Tuesdays, you'll need to run the following
query:
SELECT OrderID, OrderDate,
CustomerID, EmployeeID
FROM dbo.Orders
WHERE DATEPART(weekday,
OrderDate) = 2;
To know exactly what weekday number
to expect, you can explicitly change the
DATEFIRST setting by using the SET
DATEFIRST command. However, you
might prefer not to do so. Running that
command can affect other code running
in the session and can affect performance
in other ways that are outside the scope of
this article.
In short, the expression DATEPART
(weekday, OrderDate) is language-dependent,
and if your application serves international
users, you need to be careful. You should
write your expressions so that they're
language-independent.
One way to calculate a weekday number
in a language-independent manner is to
rely on the fact that you have access to the
current effective value of the DATEFIRST
setting via the @@DATEFIRST function.
Instead of using the expression DATEPART
(weekday,orderDate), use the expression DATEPART(weekday, OrderDate+ @@
DATEFIRST - logical_datefirst_constant). The
trick is to add @@DATEFIRST days to
the input datetime value, thereby neutralizing the effect of the DATEFIRST
setting. Think about it: If DATEFIRST
is set to some n value, you add n days to
the date you're checking. This way, the
weekday number you get won't depend
on the DATEFIRST setting. The role of logical_datefirst_constant is to let you control
what will be the logical DATEFIRST value
you want to use. For example, if you want
to logically set the DATEFIRST value to
Monday, subtract the constant 1 (which represents Monday) as the DATEFIRST value.
Try running the following expression
multiple times, setting the DATEFIRST
setting to different values. You'll see that
you always get the same weekday number
(today's), assuming Monday is the first day
of the week.
SET DATEFIRST 1;
(also try with 2, 3, 4, 5, 6, 7)
SELECT DATEPART(weekday,
GETDATE() + @@DATEFIRST - 1);
Prev. page
1
[2]
3
next page 
