A recent question on myITForum regarding a report query for ConfigMgr 2007:
In a SQL query how do I convert the data/time that is returned in 2010-04-11 19:07:00.000 format into something like 4/11/2010 10:50:26 AM so that it's more readable (in US formatting)
My query that I am getting date and time from is simply:
SELECT dbo.v_R_System.Name0, dbo.v_R_System.User_Name0,
dbo.v_GS_OPERATING_SYSTEM.LastBootUpTime0
FROM dbo.v_R_System INNER JOIN
dbo.v_GS_OPERATING_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID
There are two ways to handle this… use the DATEPART function to parse the various date parts, or use the CONVERT function to change the date displayed. The style, represented as the number 100 below, can be changed to provide different formats.
SELECT dbo.v_R_System.Name0, dbo.v_R_System.User_Name0,
CONVERT(varchar(30), dbo.v_GS_OPERATING_SYSTEM.LastBootUpTime0,100 )
FROM dbo.v_R_System INNER JOIN
dbo.v_GS_OPERATING_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID
Will give you a formatted date as below, without writing custom DATEPART functions:
Feb 6 2010 8:12AM