SQL Server Date input and output format settings

The default language option SQL Server defines 33 natural languages, each of which identifies a date Interpretation Method stored in the system table syslanguages, each language is identified by a language identifier (ID.
The default language of the SQL server instance is determined by the System Option default language. You can modify it through sp_configure:
Exec sp_configure 'default language ', [langid]
Langid is the language ID, which corresponds to the langid column of syslanguages in the system table. For example, the following code sets the default language of the current instance to English (langid = 0)
Use master
Exec sp_configure 'default language ', 0
Reconfigure with override
Each SQL server instance uses the default language for all connections to the server. However, you can set the SQL Server language for each connection.
(1) Microsoft ActiveX Data Objects and ole db applications can include the language keyword in the provider string specified during connection. For ole db applications, you can also set the special property of the provider: ssprop_init_currentlanguage before connecting.
(2) Open Database Connection (ODBC) applications can include the language keyword in the connection string specified on sqldriverconnect. ODBC applications can also specify language settings in the SQL Server ODBC Data Source definition.
(3) The db-library application can use dblogin to allocate loginrec, and then use the dbsetnatlang macro to specify language settings before calling dbopen to open the connection.
(4) Any application can use the set language statement to specify the SQL Server language.
Set datefirst
Set datefirst: set the day of the week to the day of the week. This setting is valid for all users. This setting will be retained unless it is modified again.
The specific syntax of set datefirst is as follows:
Set datefirst {number | @ number_var}
The parameter number | @ number_var is an integer that indicates the first day of a week. The value range is 1 ~ 7, 1 indicates that the first day of a week is Monday, and 7 indicates that the first day of a week is Sunday. The default value of number is 7 (that is, the first day of a week is Sunday ). You can query the system variable @ datefirst to obtain the current set datefirst value.
The results of datename (DW, date) and datepart (DW, date) are affected by the set datefirst value.

Set dateformat
Set dateformat is used to enter the order of the date (month, day, or year) of datetime or smalldatetime data. It is only used in the interpretation of converting a string to a date value. It does not affect the display of the date value.
The specific syntax of set dateformat is as follows:
Set dateformat {format | @ format_var}
The parameter format | @ format_var is the order of the date part. Valid parameters include mdy, DMY, ymd, ydm, MYD, and dym.
Set dateformat is set during execution or runtime, rather than during analysis. It only acts on the current session environment and will automatically expire after the current session environment is closed.
There are several notes for setting set dateformat.
(1) The set dateformat setting also affects the process of converting converted dates to dates using convert, but does not affect the convert processing of the specified style.
For example, in the following example, the first convert conversion does not specify a style, the conversion result is affected by the set dataformat, the second convert conversion specifies a style, and the conversion result is affected by the style.
-- Set the input date sequence to day/month/year
Set dateformat DMY

-- Convert conversion without specifying the style parameter will be affected by set dateformat
Select convert (datetime, '2-1-2005 ')
-- Result: 00:00:00. 000

-- Convert conversion of the specified style parameter is not affected by set dateformat
Select convert (datetime, '2-1-2005 ', 101)
-- Result: 00:00:00. 000
(2) If the input date contains the century part, the year interpretation is not affected by the set dateformat setting when the date is interpreted.
For example, in the following code, for the same set dateformat setting, the century part of the input date is different from the century Part Of The date that is not input.
Declare @ DT datetime

-- Set dateformat to month, day, and year.
Set dateformat mdy

-- Specify the century Part in the input date
Set @ dt = '01-2002-03'
Select @ dt
-- Result: 00:00:00. 000

-- The century section is not specified in the input date
Set @ dt = '01-02-03'
Select @ dt
-- Result: 00:00:00. 000
(3) If the input date does not contain the date separator, SQL Server ignores the set dateformat setting when interpreting the date.
For example, in the following code, if the character date that does not contain the date separator is set to dateformat, the result is the same.
Declare @ DT datetime

-- Set dateformat to month, day, and year.
Set dateformat mdy
Set @ dt = '000000'
Select @ dt
-- Result: 00:00:00. 000

-- Set dateformat to: day, month, and year
Set dateformat DMY
Set @ dt = '000000'
Select @ dt
-- Result: 00:00:00. 000

-- The input date contains the date separator.
Set @ dt = '01-02-03'
Select @ dt
-- Result: 00:00:00. 000
Set Language
Set language specifies the language environment of the session. The session language determines the datetime display format and system information.
The specific syntax of set language is as follows:
Set language {[N] 'language' | @ language_var}
The parameter [N] 'language' | @ language_var is the language name stored in the system table syslanguages. For table structure definition, see table 2-5, for the relationship between the language name and the syslanguages system table, see table 2-6. This parameter can be UNICODE or DBCS converted to Unicode (dubyte character set ). To specify a unicode language, use n'language '. If the language is specified as a variable, the Data Type of the variable must be sysname.
Set language is set during execution or running, rather than during analysis. It only acts on the current session environment and will automatically expire after the current session environment is closed.
The date processing functions affected by the set language settings are datename and convert. See table 2-6.
Table 2-6 Relationship Between set language, datename, and convert and system table syslanguages
Column name
Relationship with set language, datename, and convert
Name
Set language permission Parameters
Alias
Months
Determines the return value of datename (month, date)
Shortmonths
This affects the value of the month part in the output string when CONVERT Converts the date to a byte character, style = {6,106, 7,107, 9,109, 13,113,100 }.
Days
Determines the return value of datename (weekday, date)

The following example demonstrates the different results of the datename and convert functions in different language environments.
Use master

-- Set the language environment of the session to English.
Set language n 'inc'
Select
Datename (month, getdate () as [month],
Datename (weekday, getdate () as [weekday],
Convert (varchar, getdate (), 109) as [convert]
/* -- Result:
Month weekday convert
----------------------------------------------------------
March Tuesday Mar 15 2005 pm
--*/

-- Set the language environment of the session to simplified Chinese
Set language n 'simplified Chinese'
Select
Datename (month, getdate () as [month],
Datename (weekday, getdate () as [weekday],
Convert (varchar, getdate (), 109) as [convert]
/* -- Result
Month weekday convert
---------------------------------------------------------------------
05 thurs05 19 2005 2: 49: 20: 607pm
--*/