SQL Server date and time output formats with CONVERT()

Use this example to see the core idea, then work through the complete steps below. Code snippets are Classic ASP / VBScript or SQL Server T-SQL as labelled, not PHP.

Format a SQL Server datetime for display

SQL example

SELECT CONVERT(char(10), issue_dt, 23) AS iso_date,
       CONVERT(char(8), issue_dt, 108) AS time_24h
FROM dbo.dt;

Style 23 produces yyyy-mm-dd; style 108 produces hh:mm:ss. For storing or comparing values, retain the underlying date/time data type.

On this page — Show page sectionsHide page sections

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", conn

Style 108 produces a 24-hour hh:mm:ss representation.

22:02:00

Using 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", conn

12/22/2007

Style 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

Styles that use two-digit years can be ambiguous and some style conversions are nondeterministic. Prefer four-digit-year and ISO representations when the value will be parsed again rather than merely displayed.

Formatting versus data storage

CONVERT() with a style number can produce human-readable strings, but sorting and filtering should generally use real date/time values. Avoid converting indexed date columns in WHERE conditions.

Unambiguous output

Use style 23 for ISO-like yyyy-mm-dd and style 126 for ISO 8601 date-time text. Display formats are a presentation choice, not a substitute for parameterized data storage.

Frequently asked questions

Are these queries for SQL Server or Access?: The SQL pages use SQL Server T-SQL. Access has different date functions, identity behavior, and SQL syntax.
Can I execute these statements directly on a live database?: Start in a test database. INSERT, UPDATE, DELETE, DROP and CREATE change data or schema.
How should Classic ASP send input to a query?: Use an ADODB.Command with typed, ordered parameters and encode database values when writing HTML.

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.

About the author Watch on YouTube

Connect:

Format SQL Server date/time output with CONVERT()