Use convert for date conversion in SQL Server

The time format normally deposited in the database is yyyy-mm-ddhh:mm:ss if you want to convert to yyyy-mm-dd Short date format. You can use the CONVERT function. The following is a declaration of the CONVERT function in SQL Server Help:

Using CONVERT:

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

Parameters

Expression

Is any valid microsoft®sql Server™ 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

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

Style

Date format style, whereby datetime or smalldatetime data is converted to character data (nchar,nvarchar,char, nchar or nvarchar data type), or a string format style that will float,real, or smallmoney data into character data (nchar,nvarchar,char,varchar, nchar or nvarchar data type).

SQL Server supports data formats in Arabian styles using the Kuwaiti algorithm.

In the table, the two columns on the left represent the style values that convert datetime or smalldatetime to character data. Add 100 to the style value to get a four-bit year (yyyy) that includes century digits.

without century digits (yy)	with century digits (yyyy)	Standard	Input/Output * *
-	0 or 100 (*)	Default value	Mon dd yyyy hh:miam (or PM)
1	101	United States	Mm/dd/yyyy
2	102	Ansi	Yy.mm.dd
3	103	UK/France	Dd/mm/yy
4	104	Germany	Dd.mm.yy
5	105	Italy	Dd-mm-yy
6	106	-	DD Mon yy
7	107	-	Mon dd, yy
8	108	-	Hh:mm:ss
-	9 or 109 (*)	Default value + milliseconds	Mon dd yyyy hh:mi:ss:mmmAM (or PM)
10	110	United States	Mm-dd-yy
11	111	Japan	Yy/mm/dd
12	112	Iso	Yymmdd
-	13 or 113 (*)	European default + milliseconds	DD Mon yyyy hh:mm:ss:mmm (24h)
14	114	-	Hh:mi:ss:mmm (24h)
-	20 or 120 (*)	ODBC specification	YYYY-MM-DD HH:MM:SS[.FFF]
-	21 or 121 (*)	ODBC specification (with milliseconds)	YYYY-MM-DD HH:MM:SS[.FFF]
-	126 (* *)	ISO8601	YYYY-MM-DD Thh:mm:ss:mmm (no spaces included)
-	130*	Kuwait	DD Mon yyyy hh:mi:ss:mmmAM
-	131*	Kuwait	Dd/mm/yy Hh:mi:ss:mmmAM

* The default value (STYLE0 or 100, 9 or 109, 13 or 113, 20 or 120, 21, or 121) always returns century digits (yyyy).
* * input when converting to datetime , and output when converting to character data.
specifically for XML. For conversions from datetime or smalldatetime to character data, the output format is shown in the table. For conversions from float, money, or smallmoney to character data, the output is equivalent to Style2. For conversions from Real to character data, the output is equivalent to Style1.

important By default, SQL Server interprets two-digit years based on the cutoff year 2049. That is, the two-digit year 49 is interpreted as 2049, and the two-digit year 50 is interpreted as 1950. Many client applications, such as those based on OLE automation objects, use 2030 as the cutoff year. SQL Server provides a configuration option ("Two-digit cutoff year") to change the cutoff year used by SQL Server and to make the dates consistent. However, the safest option is to specify a four-digit year.

When converting from smalldatetime to character data, styles containing seconds or milliseconds will display zeros at these locations. When converting from a datetime or smalldatetime value, you can truncate the unwanted date part by using the appropriate char or varchar data type length.

=========================================================================

If you just take YYYY-MM-DD format time, you can use convert (nvarchar), field,120
120 is the format code, nvarchar (10) refers to the first 10 characters to be removed.

Statements and Query results:
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

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

Category: SQL Server

Use convert for date conversion in SQL Server