In SQL Server used Cast or Convert
function to Format DateTime value or column into a specific date format.Both
function are used to convert datetime to varchar or string.
CAST function
Syntax: CAST(expression as data_type)
Let's convert current date time to
varchar
CONVERT function is used to change or convert the DateTime formats.By
using convert function you can get only Date part or only Time part from the
datetime.
CONVERT Function
Syntax: CONVERT(data_type,expression,date
Format style)
Let's take Sql Server DateTtime
styles example:
Format
|
Query
|
USA mm/dd/yy
|
select convert(varchar, getdate(),
1)
|
ANSI yy.mm.dd
|
select convert(varchar, getdate(),
2)
|
British/French dd/mm/yy
|
select convert(varchar, getdate(),
3)
|
German dd.mm.yy
|
select convert(varchar, getdate(),
4)
|
Italian dd-mm-yy
|
select convert(varchar, getdate(),
5)
|
dd mon yy
|
select convert(varchar, getdate(),
6)
|
Mon dd, yy
|
select convert(varchar, getdate(),
7)
|
USA mm-dd-yy
|
select convert(varchar, getdate(),
10)
|
JAPAN yy/mm/dd
|
select convert(varchar, getdate(),
11)
|
ISO yymmdd
|
select convert(varchar, getdate(),
12)
|
mon dd yyyy hh:miAM (or PM)
|
select convert(varchar, getdate(),
100)
|
mm/dd/yyyy
|
select convert(varchar, getdate(),
101)
|
yyyy.mm.dd
|
select convert(varchar, getdate(),
102)
|
dd/mm/yyyy
|
select convert(varchar, getdate(),
103)
|
dd.mm.yyyy
|
select convert(varchar, getdate(),
104)
|
dd-mm-yyyy
|
select convert(varchar, getdate(),
105)
|
dd mon yyyy
|
select convert(varchar, getdate(),
106)
|
Mon dd, yyyy
|
select convert(varchar, getdate(),
107)
|
hh:mm:ss
|
select convert(varchar, getdate(),
108)
|
Default + milliseconds mon dd yyyy
hh:mi:ss:mmmAM (or PM)
|
select convert(varchar, getdate(),
109)
|
mm-dd-yyyy
|
select convert(varchar, getdate(),
110)
|
yyyy/mm/dd
|
select convert(varchar, getdate(),
111)
|
yyyymmdd
|
select convert(varchar, getdate(),
112)
|
Europe default + milliseconds dd
mon yyyy hh:mm:ss:mmm(24h)
|
select convert(varchar, getdate(),
113) or select convert(varchar, getdate(), 13)
|
hh:mi:ss:mmm(24h)
|
select convert(varchar, getdate(),
114)
|