Date & time formats using Convert in SELECT query from MSSQL table

SQL PHP HTML ASP JavaScript articles and free scripts to download

Formatted output of date and time field in SELECT query of MSSQL table

We may have to collect date and time in different formats based on our requirement. While using a SELECT query we can specify our format by using CONVERT . Here is an examples of collecting different types of date and time.

only time part

rs.open " select convert(char(8),issue_dt,108) as tmp from dt " , conn

Inside the convert command char(8) will collect first 8 characters of time part so it will display like this

22:02:00

So a value of char(5) will only display hour and minutes ( no seconds ) and char(2) will display only hours

Only Date Part

rs.open " select convert(char(10),issue_dt,101) as tmp from dt " , conn

From above command we will get the output like this.

12/22/2007

This is in US date format or mm/dd/yyy format

Here is the table for different style values can be sued along with convert to get required date and time formats.

0 or 100	Default	mon dd yyyy hh:miAM (or PM)
101	U.S	mm/dd/yyyy
102	ANSI	yy.mm.dd
103	British/French	dd/mm/yy
104	German	dd.mm.yy
105	Italian	dd-mm-yy
106	-	dd mon yy
107	-	Mon dd, yy
108	-	hh:mi:ss
9 or 109	Default + milliseconds	mon dd yyyy hh:mi:ss:mmmAM (or PM)
110	USA	mm-dd-yy
111	JAPAN	yy/mm/dd
112	ISO	yymmdd
13 or 113	Europe default + milliseconds	dd mon yyyy hh:mi:ss:mmm(24h)
114	-	hh:mi:ss:mmm(24h)
20 or 120	ODBC canonical	yyyy-mm-dd hh:mi:ss(24h)
21 or 121 )	ODBC canonical (with milliseconds)	yyyy-mm-dd hh:mi:ss.mmm(24h)
126	ISO8601	yyyy-mm-ddThh:mi:ss.mmm (no spaces)
127.	ISO8601 with time zone Z	yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)
130	Hijri (⁵)	dd mon yyyy hh:mi:ss:mmmAM
131	Hijri (⁵)	dd/mm/yy hh:mi:ss:mmmAM