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.
This article is written by plus2net.com team.
Be the first to post comment on this article :
plus2net.com
|