Use of DATEADD function to get various Dates

Share this blog:
  •  
  •  
  •  
  •  
  •  

Get First day of current week:

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

Get Last day of current week:

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

Get First day of current month:

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

Get Last day of current month:

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

Get First day of current quarter:

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

Get Last day of current quarter:

SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE())+1, -1)

Get First day of current year:

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

Get Last day of current year:

SELECT DATEADD(yy, DATEDIFF(yy,0,getdate())+1, -1)

Get First day of previous month:

SELECT DATEADD(mm, DATEDIFF(mm, -1, getdate()) - 2, 0)

Get Last day of previous month:

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

Get First day of previous quarter:

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

Get Last day of previous quarter:

SELECT DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0))

Leave a Reply

Your email address will not be published. Required fields are marked *

19 − seventeen =