Convert Date Formats 

SELECT CONVERT (data_type(length)),Date, DateFormatCode)
  • Data_Type - We need to define data type along with length. In the date function, we use Varchar(length) data types.
  • Date - We need to specify the date that we want to convert.
  • DateFormatCode - We need to specify DateFormatCode to convert a date in an appropriate form. We will explore more on this in the upcoming section.


Date format option & SQL convert date output

  1. 0 - [MMM DD YYYY hh:mm(AM/PM)]
  2. 1 - [MM/DD/YY] (Standard: U.S.A.)
  3. 2 - [YY.MM.DD] (Standard: ANSI)
  4. 3 - [DD/MM/YY] (Standard: British/French)
  5. 4 - [DD.MM.YY] (Standard: German)
  6. 5 - [DD-MM-YY] (Standard: Italian)
  7. 6 - [DD MMM YY] (Standard: Shortened month name)
  8. 7 - [MMM DD, YY] (Standard: Shortened month name)
  9. 8 - [HH:MM: SS] (Standard: 24 hour time)
  10. 9 - [MMM DD YYYY hh:mm:ss:mmm(AM/PM)] (Standard: Default + milliseconds)
  11. 10 - [MM-DD-YY] (Standard: USA)
  12. 11 - [YY/MM/DD] (Standard: JAPAN)
  13. 12 - [YYMMDD] (Standard: ISO)
  14. 13 - [DD MMM YYYY HH:MM:SS:MMM] (Standard: Europe default + milliseconds)
  15. 14 - [HH:MM:SS:MMM] (Standard: 24 hour time with milliseconds)
  16. 20 - [YYYY-MM-DD HH:MM:SS] (Standard: ODBC canonical)
  17. 21 - [YYYY-MM-DD HH:MM:SS.mmm] (Standard: ODBC canonical with milliseconds)
  18. 22 - [mm/dd/yy hh:mm:ss (AM/PM)] (Standard: USA with Time AM/PM)
  19. 23 - [yyyy-mm-dd]
  20. 24 - [hh:mm:ss]
  21. 25 - [mm:ss.m]
  22. 26 - [yyyy-dd-mm hh:mm:ss.mmm]
  23. 27 - [mm-dd-yyyy hh:mm:ss.mmm]
  24. 28 - [mm-yyyy-dd hh:mm:ss.mmm]
  25. 29 - [mm:ss.m]
  26. 30 - [dd-yyyy-mm hh:mm:ss.mmm]
  27. 31 - [yyyy-dd-mm]
  28. 32 - [mm-dd-yyyy]
  29. 33 - [mm-yyyy-dd]
  30. 34 - [dd/mm/yyyy]
  31. 35 - [dd-yyyy-mm]
  32. 100 - [MMM DD YYYY HH: SS (AM/PM)] (Standard: Default)
  33. 101 - [MM/DD/YYYY] (Standard: USA)
  34. 102 - [YYYY.MM.DD] (Standard: ANSI)
  35. 103 - [DD/MM/YYYY] (Standard: British / French)
  36. 104 - [DD.MM.YY] (Standard: German)
  37. 105 - [DD-MM-YY] (Standard: Italian)
  38. 106 - [DD MMM YYYY] (Standard: Shortened month name)
  39. 107 - [MMM DD,YYYY] (Standard: Shortened month name)
  40. 108 - [HH:MM: SS] (Standard: 24 hour time)
  41. 109 - [MMM DD YYYY hh:mm:ss:mmm(AM/PM)] (Standard: Default + milliseconds)
  42. 110 - [MM- DD-YY] (Standard: USA)
  43. 111 - [YYYY/MM/DD] (Standard: JAPAN)
  44. 112 - [YYYYMMDD] (Standard: ISO)
  45. 113 - [DD MMM YYYY HH:MM:SS: MMM] (Standard: Europe default + milliseconds)
  46. 114 - [HH:MM:SS: MMM] (Standard: 24 hour time with milliseconds)
  47. 120 - [YYYY-MM-DD HH:MM: SS] (Standard: ODBC canonical)
  48. 121 - [YYYY-MM-DD HH:MM: SS.mmm] (Standard: ODBC canonical with milliseconds)
  49. 126 - [YYYY-MM-DDTHH:MM: SS.mmm] (Standard: ISO8601)
  50. 127 - [YYYY-MM-DDTHH:MM: SS.mmm]
  51. 130 - [DD MMM YYYY hh:mi:ss:mmm(AM/PM)] (Standard: Islamic/Hijri date)
  52. 131 - [DD MMM YYYY hh:mm:ss:mmm(AM/PM)]