Format SQL Server date/time output with CONVERT()
Use SQL Server CONVERT() when a query specifically needs a textual date/time representation. Keep the underlying column typed as date, datetime2 or another date/time type; formatting is a presentation step.
Time only
rs.Open "SELECT CONVERT(char(8), issue_dt, 108) AS formatted_time FROM dbo.dt", connStyle 108 produces a 24-hour hh:mm:ss representation.
22:02:00Using a shorter char length truncates the displayed text, but an explicit target length matching the intended format is clearer.
Date only in U.S. display format
rs.Open "SELECT CONVERT(char(10), issue_dt, 101) AS formatted_date FROM dbo.dt", conn12/22/2007Style 101 uses mm/dd/yyyy. For machine-readable interchange, prefer an ISO style such as 23 (yyyy-mm-dd) or 126.
Common CONVERT() date/time styles
| Style | Name | Typical output |
|---|---|---|
| 0 or 100 | Default datetime | mon dd yyyy hh:miAM |
| 101 | U.S. | mm/dd/yyyy |
| 102 | ANSI | yyyy.mm.dd |
| 103 | British/French | dd/mm/yyyy |
| 104 | German | dd.mm.yyyy |
| 105 | Italian | dd-mm-yyyy |
| 106 | Day mon year | dd mon yyyy |
| 107 | Mon day, year | Mon dd, yyyy |
| 108 | Time | hh:mi:ss |
| 109 | Default + milliseconds | mon dd yyyy hh:mi:ss:mmmAM |
| 110 | USA | mm-dd-yyyy |
| 111 | Japan | yyyy/mm/dd |
| 112 | ISO compact | yyyymmdd |
| 113 | Europe + milliseconds | dd mon yyyy hh:mi:ss:mmm |
| 114 | Time + milliseconds | hh:mi:ss:mmm |
| 120 | ODBC canonical | yyyy-mm-dd hh:mi:ss |
| 121 | ODBC canonical + milliseconds | yyyy-mm-dd hh:mi:ss.mmm |
| 23 | ISO date | yyyy-mm-dd |
| 126 | ISO 8601 | yyyy-mm-ddThh:mi:ss.mmm |
| 127 | ISO 8601 with Z | yyyy-MM-ddThh:mm:ss.fffZ |
| 130 | Hijri | dd mon yyyy hh:mi:ss:mmmAM |
| 131 | Hijri | dd/mm/yyyy hh:mi:ss:mmmAM |
ASP Home
Subhendu Mohapatra
Author & Instructor at plus2net
I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.
