|
CHAPTER 2
Single-Table Queries
75
In this example, the character string literal is converted to the column’s data type (DATETIME) because
character strings are considered lower in terms of data type precedence with respect to date and time
data types. Implicit conversion rules are not always that simple, and in fact different rules are applied
with filters and in other expressions, but for the purposes of this discussion, I’ll keep things simple. For
the complete description of data type precedence, see “Data Type Precedence” in SQL Server Books
Online.
The point I’m trying to make is that in the preceding example, implicit conversion takes place be-
hind the scenes. This query is logically equivalent to the following one, which explicitly converts the
character string to a DATETIME data type.
SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderdate = CAST('20070212' AS DATETIME);
It is important to note that some character string formats of date and time literals are language
dependent, meaning that when you convert them to a date and time data type, SQL Server might
interpret the value differently based on the language setting in effect in the session. Each logon de-
fined by the database administrator has a default language associated with it, and unless it is changed
explicitly, that language becomes the effective language in the session. You can overwrite the default
language in your session by using the SET LANGUAGE command, but this is generally not recom-
mended because some aspects of the code might rely on the user’s default language.
The effective language in the session sets several language-related settings behind the scenes,
among them one called DATEFORMAT, which determines how SQL Server interprets the liter-
als you enter when they are converted from a character string type to a date and time type. The
DATEFORMAT setting is expressed as a combination of the characters d, m, and y. For example, the
us_english language setting sets the DATEFORMAT to mdy, whereas the British language setting
sets the DATEFORMAT to dmy. You can override the DATEFORMAT setting in your session by using
the SET DATEFORMAT command, but as mentioned earlier, changing language-related settings is
generally not recommended.
Consider, for example, the literal ‘02/12/2007’. SQL Server can interpret the date as either Feb-
ruary 12, 2007 or December 2, 2007 when you convert this literal to one of the following types:
DATETIME, DATE, DATETIME2, or DATETIMEOFFSET. The effective LANGUAGE/DATEFORMAT setting
is the determining factor. To demonstrate different interpretations of the same character string literal,
run the following code.
SET LANGUAGE British;
SELECT CAST('02/12/2007' AS DATETIME);
SET LANGUAGE us_english;
SELECT CAST('02/12/2007' AS DATETIME);
www.it-ebooks.info
76
Microsoft SQL Server 2012 T-SQL Fundamentals
Notice in the output that the literal was interpreted differently in the two different language
environments.
Changed language setting to British.
-----------------------
2007-12-02 00:00:00.000
Changed language setting to us_english.
-----------------------
2007-02-12 00:00:00.000
Note that the LANGUAGE/DATEFORMAT setting only affects the way the values you enter are in-
terpreted; these settings have no impact on the format used in the output for presentation purposes,
which is determined by the database interface used by the client tool (such as ODBC) and not by the
LANGUAGE/ DATEFORMAT setting. For example, OLEDB and ODBC present DATETIME values in the
format ‘ YYYY-MM-DD hh:mm:ss.nnn’.
Because the code you write might end up being used by international users with different lan-
guage settings for their logons, understanding that some formats of literals are language dependent
is crucial. It is strongly recommended that you phrase your literals in a language-neutral manner.
Language-neutral formats are always interpreted by SQL Server the same way and are not affected
by language-related settings. Table 2-2 provides literal formats that are considered neutral for each of
the date and time types.
Do'stlaringiz bilan baham: | 
