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 (5) dd mon yyyy hh:mi:ss:mmmAM
131 Hijri (5) dd/mm/yy hh:mi:ss:mmmAM
More details on style for CONVERT Command is at Micorsoft site.

Be the first to post comment on this article :


Google+

Post Comment This is for short comments only. Use the forum for more discussions.






OPINION POLL

What is the most important factor of a web site
HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us

©2000-2015 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer