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 (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.
Name
Email( not to be displayed)Privacy Policy
1+2=This is to prevent automatic submission by spammers. Please enter the result of the sum as asked



HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us

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