Share This Post

How to change SQL Server date time fields for report display

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

Share This Post

Specializing in System Center Products: Configuration Manager 2007, Operations Manager 2007, Desired Configuration Management, SMS 2003, software distribution, ITMU, SUS & WSUS. Database products and solutions: Microsoft SQL Server 2005 & 2008, Business Intelligence Development Studio, Analysis Server, Reporting Services, Integration Services. Microsoft Windows Server 2003 & 2008, SharePoint, PerformancePoint, Active Directory, DNS, Group Policy, Security, VBScript, ASP, Visual Basic, WMI, HTML and XML.

Leave a Reply