Sql server datetime format

Sometimes is useful to get on SQL server clean date time values, without the hh:mm:ss, here are the examples starting from a simple GetDate().

For example you may need the date of the first day of the week or of the first day of the month.Here is how to get these values:

select getdate() 
today date with hour
Output: 2008-02-14 17:31:13.727

select DATEADD(dd, DATEDIFF(d,0,getdate()), 0)
today
Starting:2008-02-14 17:31:13.727
Output: 2008-02-14 00:00:00.000

select DATEADD(dd, DATEDIFF(d,-1,getdate()), 0)
tomorrow
Starting:2008-02-14 17:31:13.727
Output: 2008-02-15 00:00:00.000

select DATEADD(dd, DATEDIFF(d,2,getdate()), 0)
yesterday
Starting:2008-02-14 17:31:13.727
Output: 2008-02-12 00:00:00.000

select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)this month
Starting:2008-02-14 17:31:13.727
Output: 2008-02-01 00:00:00.000

select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)monday
Starting:2008-02-14 17:31:13.727
Output: 2008-02-11 00:00:00.000

select dateadd(wk,-1,DATEADD(wk, DATEDIFF(wk,0,getdate()),0))
last week
Starting:2008-02-14 17:31:13.727
Output: 2008-02-04 00:00:00.000

select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
this year
Starting:2008-02-14 17:31:13.727
Output: 2008-01-01 00:00:00.000

select dateadd(m,-1,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
1 month ago
Starting:2008-02-14 17:31:13.727
Output: 2008-01-01 00:00:00.000

select dateadd(m,-6,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
6 months ago
Starting:2008-02-14 17:31:13.727
Output: 2007-08-01 00:00:00.000

Hope it helps,

Matteo