| | |
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.
| |
|
|
|
