sql - beginning of hour, month etc..

Question

I know how to get in SQL (SQL Server) the current date, but with the beginning of the day:

select dateadd(DAY, datediff(day, 0, getdate()),0) 
(result:2009-09-17 00:00:00.000)

I need to get (in SQL) the current date with the beginning of this hour. For example: 2009-09-17 17:00:00 (I don't care about the exact format)

and I need to get the current date but with the beginning of this month: For example: 2009-09-01 00:00:00.000 (I don't care about the exact format)

Can you help me? Thanks in advance

Answer 1

Try this

select DATEADD(hh,17,dateadd(DAY, datediff(day, 0, getdate()),0))

SELECT CAST('01 ' + CAST(DATENAME(MM, getdate()) AS VARCHAR(15)) + CAST(DATEPART(yyyy, GETDATE()) AS VARCHAR(5)) AS DATETIME)

Answer 2

Just adapt your current start of day code!

All you want is start of month, start of hour. Its just the same...

select dateadd(month, datediff(month, 0, getdate()),0) 

select dateadd(hour, datediff(hour, 0, getdate()),0)