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 " 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
So a value of char(5) will only display hour and minutes ( no seconds ) and char(2) will display only hours

Only Date Part " select  convert(char(10),issue_dt,101) as tmp from dt  " , conn
From above command we will get the output like this.
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
103 British/French dd/mm/yy
104 German
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 :

Post your comments , suggestion , error , requirements etc here .

We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2024 All rights reserved worldwide Privacy Policy Disclaimer