Steve Ngai (危 志 荣) KuaLa Lumpur, Malaysia

Convert DateTime Data Type

(This example was done using SQL Query Analyzer in SQL Server 2000)

Say you have this table below,

product_type log_datetime
xbox 2008-06-11 16:26:46.000
xbox 2008-06-13 16:30:09.653
psp 2008-06-14 16:28:08.000
ps 2008-06-16 16:21:40.000

And you would like to display log_datetime in different format, how do you do it? One way is to do the formating at the query side. Example, if you want the date format to be dd mmm yyyy format, use CONVERT to format log_datetime like below

SELECT product_type, convert(varchar(11),log_datetime,106) AS log_date FROM test ORDER BY log_datetime

The output becomes,

product_type log_datetime
xbox 11 Jun 2008
xbox 13 Jun 2008
psp 14 Jun 2008
ps 16 Jun 2008

If you want to just show the time only, just change the 106 to 108 to become,

SELECT product_type, convert(varchar(11),log_datetime,108) AS log_date FROM test ORDER BY log_datetime

The output becomes,

product_type log_datetime
xbox 16:26:46
xbox 16:30:09
psp 16:28:08
ps 16:21:40

For more reference, http://doc.ddart.net/mssql/sql70/ca-co_1.htm