SQL Server conversion between date and string

When a character is converted to a date, the use of the style

--1. Style=101, represents the date string as: mm/dd/yyyy format
SELECT CONVERT (datetime, ' 11/1/2003 ', 101)
--Results: 2003-11-01 00:00:00.000

--2. Style=101, represents the date string as: dd/mm/yyyy format
SELECT CONVERT (datetime, ' 11/1/2003 ', 103)
--Results: 2003-01-11 00:00:00.000

/*== date conversion to string ==*/
DECLARE @dt datetime
SET @dt = ' 2003-1-11 '

--1. Style=101, converts the date to: mm/dd/yyyy format
SELECT CONVERT (varchar, @dt, 101)
--Results: 01/11/2003

--2. style=103, converts the date to: dd/mm/yyyy format
SELECT CONVERT (varchar, @dt, 103)
--Results: 11/01/2003

/*== This is a common mistake many people make, a style style that uses dates for non-date conversions ==*/
SELECT CONVERT (varchar, ' 2003-1-11 ', 101)
--Results: 2003-1-11

--1.
/*--description
The set DateFormat setting also has an effect on converting a character date to a date using convert
It does not affect the convert process, which explicitly specifies the style.
--*/

--example, in the following example, the first convert transformation does not specify a style, the result of the conversion is affected by the set DataFormat, the second convert transformation specifies the style, and the result is affected by the style.
--Set input date order to day/month/year
SET DateFormat DMY

--Convert conversions that do not specify the style parameter will be affected by the set DateFormat
SELECT CONVERT (datetime, ' 2-1-2005 ')
--Results: 2005-01-02 00:00:00.000

--Specifies that the convert transform of the style parameter is not affected by set DateFormat
SELECT CONVERT (datetime, ' 2-1-2005 ', 101)
--Results: 2005-02-01 00:00:00.000
GO

--2.
/*--description

If the date entered contains the century part, the date is interpreted and processed
The interpretation of the year is not affected by the set DateFormat setting.
--*/

--example, in the following code, the same set DateFormat setting, the century part of the input date and the century part that does not enter the date, the interpretation of the date result is different.
DECLARE @dt datetime

--setting set DateFormat to: Month Day year
SET DateFormat MDY

--Enter the date specified in the century part
SET @dt = ' 01-2002-03 '
SELECT @dt
--Results: 2002-01-03 00:00:00.000

--the date entered does not specify the century part
SET @dt = ' 01-02-03 '
SELECT @dt
--Results: 2003-01-02 00:00:00.000
GO

--3.
/*--description

If the date entered does not contain a date separator, SQL Server interprets the date
Setting for set DATEFORMAT is ignored.
--*/

--example, in the following code, the character date that does not contain the date separator, under different set DateFormat settings, is the same as the result of the interpretation.
DECLARE @dt datetime

--setting set DateFormat to: Month Day year
SET DateFormat MDY
SET @dt = ' 010203 '
SELECT @dt
--Results: 2001-02-03 00:00:00.000

--Setting set DATEFORMAT is: Sun and moon Year
SET DateFormat DMY
SET @dt = ' 010203 '
SELECT @dt
--Results: 2001-02-03 00:00:00.000

--Enter a date that contains a date separator
SET @dt = ' 01-02-03 '
SELECT @dt
--Results: 2003-02-01 00:00:00.000

--The following example shows the different results of datename and the CONVERT function under different locales (SET LANGUAGE).
Use master

--Set the locale for the session to: 中文版
SET LANGUAGE N ' 中文版 '
SELECT
Datename (Month,getdate ()) as [Month],
Datename (Weekday,getdate ()) as [Weekday],
Convert (Varchar,getdate (), 109) as [convert]
/*--results:
Month Weekday CONVERT
------------- -------------- -------------------------------
March Tuesday Mar 2005 8:59pm
--*/

--Set the locale for the session: Simplified Chinese
SET LANGUAGE N ' Simplified Chinese '
SELECT
Datename (Month,getdate ()) as [Month],
Datename (Weekday,getdate ()) as [Weekday],
Convert (Varchar,getdate (), 109) as [convert]
/*--Results
Month Weekday CONVERT

SQL Server conversion between date and string