Syntax
Using CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
Arguments
expression
Is any valid Microsoft® SQL Server™ expression. For more information, see Expressions.
data_type
Is the target system-supplied data type, including bigint and sql_variant. User-defined data types cannot be used. For more information about available data types, see Data Types.
length
Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types.
style
Is the style of date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or the string format when converting float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).
Without century (yy) | With century (yyyy) | Standard | Input/Output** |
---|---|---|---|
– | 0 or 100 (*) | Default | mon dd yyyy hh:miAM (or PM) |
1 | 101 | USA | mm/dd/yy |
2 | 102 | ANSI | yy.mm.dd |
3 | 103 | British/French | dd/mm/yy |
4 | 104 | German | dd.mm.yy |
5 | 105 | Italian | dd-mm-yy |
6 | 106 | – | dd mon yy |
7 | 107 | – | Mon dd, yy |
8 | 108 | – | hh:mm:ss |
– | 9 or 109 (*) | Default + milliseconds | mon dd yyyy hh:mi:ss:mmmAM (or PM) |
10 | 110 | USA | mm-dd-yy |
11 | 111 | JAPAN | yy/mm/dd |
12 | 112 | ISO | yymmdd |
– | 13 or 113 (*) | Europe default + milliseconds | dd mon yyyy hh:mm:ss:mmm(24h) |
14 | 114 | – | hh:mi:ss:mmm(24h) |
– | 20 or 120 (*) | ODBC canonical | yyyy-mm-dd hh:mi:ss(24h) |
– | 21 or 121 (*) | ODBC canonical (with milliseconds) | yyyy-mm-dd hh:mi:ss.mmm(24h) |
– | 126(***) | ISO8601 | yyyy-mm-dd Thh:mm:ss.mmm(no spaces) |
– | 130* | Hijri**** | dd mon yyyy hh:mi:ss:mmmAM |
– | 131* | Hijri**** | dd/mm/yy hh:mi:ss:mmmAM |
Example
select CONVERT(varchar, getdate(), 120 )
2004-09-12 11:06:08
select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),’-‘,”),’ ‘,”),’:’,”)
20040912110608
select CONVERT(varchar(12) , getdate(), 111 )
2004/09/12
select CONVERT(varchar(12) , getdate(), 112 )
20040912
select CONVERT(varchar(12) , getdate(), 102 )
2004.09.12
select CONVERT(varchar(12) , getdate(), 101 )
09/12/2004
select CONVERT(varchar(12) , getdate(), 103 )
12/09/2004
select CONVERT(varchar(12) , getdate(), 104 )
12.09.2004
select CONVERT(varchar(12) , getdate(), 105 )
12-09-2004
select CONVERT(varchar(12) , getdate(), 106 )
12 09 2004
select CONVERT(varchar(12) , getdate(), 107 )
09 12, 2004
select CONVERT(varchar(12) , getdate(), 108 )
11:06:08
select CONVERT(varchar(12) , getdate(), 109 )
09 12 2004 1
select CONVERT(varchar(12) , getdate(), 110 )
09-12-2004
select CONVERT(varchar(12) , getdate(), 113 )
12 09 2004 1
select CONVERT(varchar(12) , getdate(), 114 )
11:06:08.177
Hi I would like to know where you got this blog template from I adore it!
It’s from the wordpress website. The name is “Fresh Editorial”.