MS SQL – Strip Time, Day, Month from DateTime

In the database, Date Time values is stored to millisecond level and for many times, I need to group record by Day, Week or Month or Year and I want to get day only or start day of the week or start day of the month, etc. Here I just put a note on SQL for the purpose mentioned.

select getdate() as CurrentDateTime;



select DATEADD(d,DATEDIFF(d,0,getdate()),0) as CurrentDate;


select DATEADD(wk,DATEDIFF(wk,0,getdate()),0) as CurrentStartDayOftheWeek;


select DATEADD(mm,DATEDIFF(mm,0,getdate()),0) as CurrentStartDayOftheMonth;


select DATEADD(yy,DATEDIFF(yy,0,getdate()),0) as CurrentStartDayOftheYear;


Result

CurrentDateTime
-----------------------
2011-05-10 11:18:54.857
CurrentDate
-----------------------
2011-05-10 00:00:00.000
CurrentStartDayOftheWeek
------------------------
2011-05-09 00:00:00.000
CurrentStartDayOftheMonth
-------------------------
2011-05-01 00:00:00.000
CurrentStartDayOftheYear
------------------------
2011-01-01 00:00:00.000