SQL Server convert () function

ArticleDirectory

• Syntax

SQL Server convert () function

 

Definition and usage

The convert () function is a general function that converts a date to a new data type.

The convert () function can display date/time data in different formats.

Syntax

 
Convert (Data_type (length),Data_to_be_converted,Style)
 

Data_type (length)Specifies the target data type (with an optional length ).Data_to_be_convertedContains the value to be converted.StyleSpecifies the output format of the date/time.

AvailableStyleValue:

Style ID	Style format
100 or 0	Mon dd yyyy hh: miam (or pm)
101	Mm/DD/yy
102	YY. Mm. dd
103	Dd/mm/yy
104	Dd. mm. yy
105	DD-mm-yy
106	Dd mon YY
107	Mon DD, YY
108	Hh: mm: SS
109 or 9	Mon dd yyyy hh: MI: SS: mmmam (or pm)
110	Mm-dd-yy
111	YY/MM/dd
112	Yymmdd
113 or 13	Dd mon yyyy hh: mm: SS: Mmm (24 h)
114	Hh: MI: SS: Mmm (24 h)
120 or 20	Yyyy-mm-dd hh: MI: SS (24 h)
121 or 21	Yyyy-mm-dd hh: MI: Ss. Mmm (24 h)
126	Yyyy-mm-ddthh: mm: Ss. Mmm (no space)
130	Dd mon yyyy hh: MI: SS: mmmam
131	Dd/mm/yy hh: MI: SS: mmmam

Instance

The following script uses the convert () function to display different formats. We will use the getdate () function to obtain the current date/time:

 
Convert (varchar (19), getdate () convert (varchar (10), getdate (), 110) converter (varchar (11), getdate (), 106) convert (varchar (24), getdate (), 113)
 

The result is similar:

 
Dec 29 2008 16:25:46 PM12-29-200829 dec 0829 Dec 2008. 635