MSSQL DateTime Conversions

Sometimes a basic function like MSSQL Format Date Function can not work as you do not have permission to create a function or you simply need something quick.

To get day of week from DateTime column and order it by the proper day of week order. This is rather easy to update for those that don’t start their calendar week with Sunday.

SELECT DATENAME(weekday, [datetime]) AS DayOfWeek
,COUNT(DATENAME(weekday, [datetime])) AS TotalByDay
FROM pageview
GROUP BY DATENAME(weekday, [datetime])
ORDER BY CASE
WHEN DATENAME(weekday, [datetime]) = 'Sunday'
THEN 1
WHEN DATENAME(weekday, [datetime]) = 'Monday'
THEN 2
WHEN DATENAME(weekday, [datetime]) = 'Tuesday'
THEN 3
WHEN DATENAME(weekday, [datetime]) = 'Wednesday'
THEN 4
WHEN DATENAME(weekday, [datetime]) = 'Thursday'
THEN 5
WHEN DATENAME(weekday, [datetime]) = 'Friday'
THEN 6
ELSE 7
END

To get the month of the year from DateTime column and order it by the proper month of the year order.

SELECT DATENAME(month, [datetime]) AS DayOfWeek
,COUNT(DATENAME(month, [datetime])) AS TotalByDay
FROM pageview
GROUP BY DATENAME(month, [datetime])
ORDER BY CASE
WHEN DATENAME(month, [datetime]) = 'January'
THEN 1
WHEN DATENAME(month, [datetime]) = 'February'
THEN 2
WHEN DATENAME(month, [datetime]) = 'March'
THEN 3
WHEN DATENAME(month, [datetime]) = 'April'
THEN 4
WHEN DATENAME(month, [datetime]) = 'May'
THEN 5
WHEN DATENAME(month, [datetime]) = 'June'
THEN 6
WHEN DATENAME(month, [datetime]) = 'July'
THEN 7
WHEN DATENAME(month, [datetime]) = 'August'
THEN 8
WHEN DATENAME(month, [datetime]) = 'September'
THEN 9
WHEN DATENAME(month, [datetime]) = 'October'
THEN 10
WHEN DATENAME(month, [datetime]) = 'November'
THEN 11
WHEN DATENAME(month, [datetime]) = 'December'
THEN 12
END

To get the hour of day from DateTime column with AM PM indicator for those that do not like to show international format time.

SELECT CASE
WHEN DATEPART(hh, [datetime]) > 12
THEN convert(VARCHAR(2), DATEPART(hh, [datetime]) - 12) + ' PM'
WHEN DATEPART(hh, [datetime]) = 12
THEN convert(VARCHAR(2), DATEPART(hh, [datetime])) + ' PM'
WHEN DATEPART(hh, [datetime]) = '00'
THEN '12 AM'
WHEN DATEPART(hh, [datetime]) < 12
THEN convert(VARCHAR(2), DATEPART(hh, [datetime])) + ' AM'
END AS HourOfDay
,COUNT(DATEPART(hh, [datetime])) AS TotalByHour
FROM pageview
GROUP BY DATEPART(hh, [datetime])
ORDER BY DATEPART(hh, [datetime])