I often need to format a date column from SQL into something nicer. The code below does that.
CAST(DATEPART(m, DateCol) AS varchar(2)) +
'/' +
CAST(DATEPART(d, DateCol) AS varchar(2)) +
'/' +
CAST(DATEPART(year, DateCol) AS varchar(4)) +
' ' +
CASE WHEN DATEPART(HH, DateCol) < 13 THEN RIGHT(REPLICATE('', 2) +
CAST(datepart(HH, DateCol) AS VARCHAR(2)), 2) ELSE RIGHT(REPLICATE('', 2) +
CAST(datepart(HH, dateadd(HH, - 12, DateCol)) AS VARCHAR(2)), 2) END +
':' +
RIGHT(REPLICATE('0', 2) +
CAST(DATEPART(n, DateCol) AS VARCHAR(2)), 2) +
':' +
RIGHT(REPLICATE('0', 2) + CAST(DATEPART(ss, DateCol) AS VARCHAR(2)), 2) +
' ' +
CASE WHEN DATEPART(HH, DateCol) < 12 THEN 'AM' ELSE 'PM' END
AS FormattedDate