X
HOME HTML5 CSS3 JAVASCRIPT SQL PHP5 JQUERY ANGULAR BOOTSTRAP3 XML MORE...
REFERENCES | EXAMPLES | FORUM | ABOUT

SQL Server CONVERT() Function


SQL Dates SQL Server Date Functions

Definition and Usage

The CONVERT() function is a general function that converts an expression of one data type to another.

The CONVERT() function can be used to display date/time data in different formats.

Syntax

CONVERT(data_type(length),expression,style)

Value Description
data_type(length) Specifies the target data type (with an optional length)
expression Specifies the value to be converted
style Specifies the output format for the date/time

The table below represent the style values for datetime or smalldatetime conversion to character data:

Value
(century yy)
Value
(century yyyy)
Input/Output Standard
-0 or 100mon dd yyyy hh:miAM (or PM)Default
1101mm/dd/yyUSA
2102yy.mm.ddANSI
3103dd/mm/yyBritish/French
4104dd.mm.yyGerman
5105dd-mm-yyItalian
6106dd mon yy 
7107Mon dd, yy 
8108hh:mm:ss 
-9 or 109mon dd yyyy hh:mi:ss:mmmAM (or PM) Default+millisec
10110mm-dd-yyUSA
11111yy/mm/ddJapan
12112yymmddISO
-13 or 113dd mon yyyy hh:mi:ss:mmm (24h) 
14114hh:mi:ss:mmm (24h) 
-20 or 120yyyy-mm-dd hh:mi:ss (24h) 
-21 or 121yyyy-mm-dd hh:mi:ss.mmm (24h) 
-126yyyy-mm-ddThh:mi:ss.mmm (no spaces) ISO8601
-130dd mon yyyy hh:mi:ss:mmmAMHijiri
-131dd/mm/yy hh:mi:ss:mmmAMHijiri


Example

The following script uses the CONVERT() function to display different formats. We will use the GETDATE() function to get the current date/time:

CONVERT(VARCHAR(19),GETDATE())
CONVERT(VARCHAR(10),GETDATE(),10)
CONVERT(VARCHAR(10),GETDATE(),110)
CONVERT(VARCHAR(11),GETDATE(),6)
CONVERT(VARCHAR(11),GETDATE(),106)
CONVERT(VARCHAR(24),GETDATE(),113)

The result would look something like this:

Nov 04 2011 11:45 PM
11-04-11
11-04-2011
04 Nov 11
04 Nov 2011
04 Nov 2011 11:45:34:243


SQL Dates SQL Server Date Functions

Your suggestion:

Close [X]

Thank You For Helping Us!

Your message has been sent to W3Schools.

Close [X]
Search w3schools.com:

WEB HOSTING

UK Reseller Hosting

WEB BUILDING

Download XML Editor FREE Website BUILDER Free HTML5 Templates

SHARE THIS PAGE

facebook