Date and Time Conversions Using SQL Server

 

select convert(varchar, getdate(), 1) mm/dd/yy 12/30/06

select convert(varchar, getdate(), 2) yy.mm.dd 06.12.30

select convert(varchar, getdate(), 3) dd/mm/yy 30/12/06

select convert(varchar, getdate(), 4) dd.mm.yy 30.12.06

select convert(varchar, getdate(), 5) dd-mm-yy 30-12-06

select convert(varchar, getdate(), 6) dd-Mon-yy 30 Dec 06

select convert(varchar, getdate(), 7) Mon dd, yy Dec 30, 06

select convert(varchar, getdate(), 10) mm-dd-yy 12-30-06

select convert(varchar, getdate(), 11) yy/mm/dd 06/12/30

select convert(varchar, getdate(), 12) yymmdd 061230

select convert(varchar, getdate(), 23) yyyy-mm-dd 2006-12-30

select convert(varchar, getdate(), 101) mm/dd/yyyy 12/30/2006

select convert(varchar, getdate(), 102) yyyy.mm.dd 2006.12.30

select convert(varchar, getdate(), 103) dd/mm/yyyy 30/12/2006

select convert(varchar, getdate(), 104) dd.mm.yyyy 30.12.2006

select convert(varchar, getdate(), 105) dd-mm-yyyy 30-12-2006

select convert(varchar, getdate(), 106) dd Mon yyyy 30 Dec 2006

select convert(varchar, getdate(), 107) Mon dd, yyyy Dec 30, 2006

select convert(varchar, getdate(), 110) mm-dd-yyyy 12-30-2006

select convert(varchar, getdate(), 111) yyyy/mm/dd 2006/12/30

select convert(varchar, getdate(), 112) yyyymmdd 20061230

TIME ONLY FORMATS

8 select convert(varchar, getdate(), 8) hh:mm:ss 00:38:54

14 select convert(varchar, getdate(), 14) hh:mm:ss:nnn 00:38:54:840

24 select convert(varchar, getdate(), 24) hh:mm:ss 00:38:54

108 select convert(varchar, getdate(), 108) hh:mm:ss 00:38:54

114 select convert(varchar, getdate(), 114) hh:mm:ss:nnn 00:38:54:840

DATE & TIME FORMATS

0 select convert(varchar, getdate(), 0) Mon dd yyyy hh:mm AM/PM Dec 30 2006 12:38AM

9 select convert(varchar, getdate(), 9) Mon dd yyyy hh:mm:ss:nnn AM/PM Dec 30 2006 12:38:54:840AM

13 select convert(varchar, getdate(), 13) dd Mon yyyy hh:mm:ss:nnn AM/PM 30 Dec 2006 00:38:54:840AM

20 select convert(varchar, getdate(), 20) yyyy-mm-dd hh:mm:ss 2006-12-30 00:38:54

21 select convert(varchar, getdate(), 21) yyyy-mm-dd hh:mm:ss:nnn 2006-12-30 00:38:54.840

22 select convert(varchar, getdate(), 22) mm/dd/yy hh:mm:ss AM/PM 12/30/06 12:38:54 AM

25 select convert(varchar, getdate(), 25) yyyy-mm-dd hh:mm:ss:nnn 2006-12-30 00:38:54.840

100 select convert(varchar, getdate(), 100) Mon dd yyyy hh:mm AM/PM Dec 30 2006 12:38AM

109 select convert(varchar, getdate(), 109) Mon dd yyyy hh:mm:ss:nnn AM/PM Dec 30 2006 12:38:54:840AM

113 select convert(varchar, getdate(), 113) dd Mon yyyy hh:mm:ss:nnn 30 Dec 2006 00:38:54:840

120 select convert(varchar, getdate(), 120) yyyy-mm-dd hh:mm:ss 2006-12-30 00:38:54

121 select convert(varchar, getdate(), 121) yyyy-mm-dd hh:mm:ss:nnn 2006-12-30 00:38:54.840

126 select convert(varchar, getdate(), 126) yyyy-mm-dd T hh:mm:ss:nnn 2006-12-30T00:38:54.840

127 select convert(varchar, getdate(), 127) yyyy-mm-dd T hh:mm:ss:nnn 2006-12-30T00:38:54.840

ISLAMIC CALENDAR DATES

130 select convert(nvarchar, getdate(), 130) dd mmm yyyy hh:mi:ss:nnn AM/PM  

131 select convert(nvarchar, getdate(), 131) dd mmm yyyy hh:mi:ss:nnn AM/PM 10/12/1427 12:38:54:840AM

You can also format the date or time without dividing characters, as well as concatenate the date and time string:

Sample statement Format Output

select replace(convert(varchar, getdate(),101),'/','') mmddyyyy 12302006

select replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','') mmddyyyyhhmmss 12302006004426