FIRST AND LAST Day of Month , Year

In SQL, very often we need to find First Day or Last Day of Year, Quarter, Month or Week, when we develop in SQL Server. To make it simple we will get it from below :

 

----Today
SELECT GETDATE()

----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)))

OR

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

----First Day of Last Month
SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))

----Last Day of Last Month
SELECT DATEADD(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
SELECT DATEADD(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
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)))

-----Monthly Week No.

SELECT DATEPART(WW,GETDATE())-DATEPART(WW,DATEADD(DD,1-DAY(GETDATE()),GETDATE())) + 1

----First Day Of Current Quarter
SELECT DATEADD(QQ, DATEDIFF(QQ,0, GETDATE()), 0)

---Last Day Of Current Quarter

SELECT DATEADD(MS,-3,DATEADD(QQ, DATEDIFF(QQ,0, GETDATE())+1, 0))

----First Day Of Prior Quarter
SELECT DATEADD(QQ, DATEDIFF(QQ,0, GETDATE())-1, 0)

----Last Day Of Prior Quarter
SELECT DATEADD(MS,-3,DATEADD(QQ, DATEDIFF(QQ,0, GETDATE()), 0))

---TO GET THE MONTH NUMBER FROM A MONTHNAME:

SELECT DATEPART(mm,CAST('JUL'+ ' 1900' AS DATETIME))

---Convert an YEAR into a DATETIME:

SELECT CAST('2015' AS DateTime)

---Convert a Month Number into Month Name :

SELECT DATENAME(MM, STR(@MONTH) + '/1/1900')

And so on ......

 Reference: Govind Badkur(http://govindbadkur.blogspot.com)