Conversion of dates to strings and date formats in SQL Server

Recent projects used a lot of dates to convert strings, strings converted to date methods, but also used a lot of date format, special collation as follows, for later use.

1. Convert date to string, date format

Use the function convert:

CONVERT (data_type [(length)], expression [, style])

Parameters

Expression

Is any valid Microsoft?nbsp;sql server? An expression:

Data_type

The data types provided by the target system, including bigint and sql_variant. You cannot use a user-defined data type.

Length

Optional parameters for nchar, nvarchar, char, varchar, binary, or varbinary data types.

Style

A date format style whereby DateTime or smalldatetime data is converted to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data type) or a string format style that will float, Real, money, or smallmoney data is converted to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).

Case:

Select CONVERT (varchar), GETDATE (), 0): 2006 10:57am

Select CONVERT (varchar), GETDATE (), 1): 05/16/06

Select CONVERT (varchar), GETDATE (), 2): 06.05.16

Select CONVERT (varchar), GETDATE (), 3): 16/05/06

Select CONVERT (varchar), GETDATE (), 4): 16.05.06

Select CONVERT (varchar), GETDATE (), 5): 16-05-06

Select CONVERT (varchar), GETDATE (), 6): 16 05 06

Select CONVERT (varchar), GETDATE (), 7): 05 16, 06

Select CONVERT (varchar), GETDATE (), 8): 10:57:46

Select CONVERT (varchar), GETDATE (), 9): 2006 10:57:46:827am

Select CONVERT (varchar), GETDATE (), 10): 05-16-06

Select CONVERT (varchar), GETDATE (), 11): 06/05/16

Select CONVERT (varchar), GETDATE (), 12): 060516

Select CONVERT (varchar), GETDATE (), 13): 16 05 2006 10:57:46:937

Select CONVERT (varchar), GETDATE (), 14): 10:57:46:967

Select CONVERT (varchar), GETDATE (), 20): 2006-05-16 10:57:47

Select CONVERT (varchar), GETDATE (), 21): 2006-05-16 10:57:47.157

Select CONVERT (varchar), GETDATE (): 05/16/06 10:57:47 AM

Select CONVERT (varchar), GETDATE (), 23): 2006-05-16

Select CONVERT (varchar), GETDATE (), 24): 10:57:47

Select CONVERT (varchar), GETDATE (), 25): 2006-05-16 10:57:47.250

Select CONVERT (varchar), GETDATE (), (+): 2006 10:57am

Select CONVERT (varchar), GETDATE (), 101): 05/16/2006

Select CONVERT (varchar), GETDATE (), 102): 2006.05.16

Select CONVERT (varchar), GETDATE (), 103): 16/05/2006

Select CONVERT (varchar), GETDATE (), 104): 16.05.2006

Select CONVERT (varchar), GETDATE (), 105): 16-05-2006

Select CONVERT (varchar), GETDATE (), 106): 16 05 2006

Select CONVERT (varchar), GETDATE (), 107): 05 16, 2006

Select CONVERT (varchar), GETDATE (), 108): 10:57:49

Select CONVERT (varchar), GETDATE (), 109): 2006 10:57:49:437am

Select CONVERT (varchar), GETDATE (), 110): 05-16-2006

Select CONVERT (varchar), GETDATE (), 111): 2006/05/16

Select CONVERT (varchar), GETDATE (), 112): 20060516

Select CONVERT (varchar), GETDATE (), 113): 16 05 2006 10:57:49:513

Select CONVERT (varchar), GETDATE (), 114): 10:57:49:547

Select CONVERT (varchar), GETDATE (), 120): 2006-05-16 10:57:49

Select CONVERT (varchar), GETDATE (), 121): 2006-05-16 10:57:49.700

Select CONVERT (varchar), GETDATE (), 126): 2006-05-16t10:57:49.827

Select CONVERT (varchar), GETDATE (), ():???? ?????? 1427 10:57:49:907am

Select CONVERT (varchar), GETDATE (), 131): 18/04/1427 10:57:49:920am

2. String conversion to date

Using CAST:

CAST (expression as data_type)

Using CONVERT:

CONVERT (data_type[(length)], expression [, style])

Parameters

Expression

is any effective microsoft?nbsp; SQL Server? An expression. For more information, see expressions.

Data_type

The data types provided by the target system, including bigint and sql_variant. You cannot use a user-defined data type. For more information about the available data types, see Data types.

Length

Optional parameters for nchar, nvarchar, char, varchar, binary, or varbinary data types.

Style

A date format style whereby DateTime or smalldatetime data is converted to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data type) or a string format style that will float, Real, money, or smallmoney data is converted to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).

Case:

Select cast (2009-01-01 as DateTime)

3. Several experiences:

When converting a string to a date, SQL Server is really smart, and a format like this can be converted successfully: 20130608, 2013-06-08, 2013/06/08, even: 130608 can be converted.

In addition, two fields in SQL Server are merged into one field, or two strings are combined into a single string, which can be concatenated with a plus sign ().

Conversion of dates to strings and date formats in SQL Server