Convert DateTime Data Type

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

Say you have this table below,

product_typelog_datetime
xbox2008-06-11 16:26:46.000
xbox2008-06-13 16:30:09.653
psp2008-06-14 16:28:08.000
ps2008-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_typelog_datetime
xbox11 Jun 2008
xbox13 Jun 2008
psp14 Jun 2008
ps16 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_typelog_datetime
xbox16:26:46
xbox16:30:09
psp16:28:08
ps16:21:40

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

Leave a Reply

Your email address will not be published. Required fields are marked *

*