DateTime Functions

To get current date

SELECT GETDATE()

To get yesterday

SELECT DATEADD(d,-1,GETDATE())

First Day of Current Week

SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)

Last Day of Current Week

SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)

First Day of Last Week

SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0)

Last Day of Last Week

SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6)

First Day of Current Month

SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)

Last Day of Current Month

SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)))

First Day of Last Month

SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))

Last Day of Last Month

SELECTDATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)))

First Day of Current Year

SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)

Last Day of Current Year

SELECTDATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)))

First Day of Last Year

SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))

Last Day of Last Year

SELECTDATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)))