One area I’ve always felt MSSQL was lacking is handling dates. This week I needed to declare a date variable and select the beginning of the previous hour. I cobbled together a solution that I thought would be useful to add to the blogosphere. Below is a screenshot and the code you can copy and paste.


SELECT CAST(DATEPART(YEAR, GETDATE()) AS VARCHAR) AS 'Year'
SELECT CAST(DATEPART(MONTH, GETDATE()) AS VARCHAR) AS 'Month'
SELECT CAST(DATEPART(DAY, GETDATE()) AS VARCHAR) AS 'Day'
SELECT CAST(DATEPART(HOUR, GETDATE()) AS VARCHAR) AS 'Hour'

SELECT CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR) +
'/' +
CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR) +
'/' +
CAST(DATEPART(DAY,GETDATE()) AS VARCHAR) +
SPACE(1) +
CAST(DATEPART(HOUR,GETDATE()) - 1 AS VARCHAR) +
':00:00.000' AS 'Beginning Of Prvious Hour'