getdate() = Jan 14 2010 10:00:05:190PM

Format Query Result
USA mm/dd/yy select convert(varchar, getdate(), 1) 01/14/10
ANSI yy.mm.dd select convert(varchar, getdate(), 2) 10.01.14
British/French dd/mm/yy select convert(varchar, getdate(), 3) 14/01/10
German dd.mm.yy select convert(varchar, getdate(), 4) 14.01.10
Italian dd-mm-yy select convert(varchar, getdate(), 5) 14-01-10
dd mon yy select convert(varchar, getdate(), 6) 14 Jan 10
Mon dd, yy select convert(varchar, getdate(), 7) Jan 14, 10
HH:MI:SS select convert(varchar, getdate(), 8 ) 21:54:31
Mon dd yyyy H:MI:SS:msAM/PM select convert(varchar, getdate(), 9) Jan 14 2010 9:54:56:490PM
USA mm-dd-yy select convert(varchar, getdate(), 10) 01-14-10
JAPAN yy/mm/dd select convert(varchar, getdate(), 11) 10/01/14
ISO yymmdd select convert(varchar, getdate(), 12) 100114
dd Mon yyyy HH:MI:MS:SS select convert(varchar, getdate(), 13) 14 Jan 2010 21:57:39:070
HH:MI:MS:SS select convert(varchar, getdate(), 14) 21:58:21:263
mon dd yyyy hh:miAM (or PM) select convert(varchar, getdate(), 100) Jan 14 2010 9:59PM
mm/dd/yyyy select convert(varchar, getdate(), 101) 01/14/2010
yyyy.mm.dd select convert(varchar, getdate(), 102) 2010.01.14
dd/mm/yyyy select convert(varchar, getdate(), 103) 14/01/2010
dd.mm.yyyy select convert(varchar, getdate(), 104) 14.01.2010
dd-mm-yyyy select convert(varchar, getdate(), 105) 14-01-2010
dd mon yyyy select convert(varchar, getdate(), 106) 14 Jan 2010
Mon dd, yyyy select convert(varchar, getdate(), 107) Jan 14, 2010
hh:mm:ss select convert(varchar, getdate(), 108) 21:59:59
Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM) select convert(varchar, getdate(), 109) Jan 14 2010 10:00:05:190PM
mm-dd-yyyy select convert(varchar, getdate(), 110) 01-14-2010
yyyy/mm/dd select convert(varchar, getdate(), 111) 2010/01/14
yyyymmdd select convert(varchar, getdate(), 112) 20100114
Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h) select convert(varchar, getdate(), 113) or select convert(varchar, getdate(), 13) 14 Jan 2010 22:00:35:107
hh:mi:ss:mmm(24h) select convert(varchar, getdate(), 114) 22:00:40:423

References:
http://www.technoreader.com/SQL-Server-Date-Time-Format.aspx
http://www.sql-server-helper.com/tips/date-formats.aspx

  • Share/Bookmark