First and Last Date of Current Month,Previous Month or Current Year in SQL

First and Last Date of Current Month, Previous Month or Current Year in SQL

In my Previous post I have written on how to fetch First and Last Date of Current Month, Previous Month or Current Year in SSRS So, Now in this post. I will tell how to achieve the Same Result in SQL

Below are the queries 

Current Date

select GETDATE()

First date of Current Month or Current month first date

select dateadd(mm,datediff(mm,0,getdate()),0) 

select DATEADD(DAY, -day(DATEADD(mm,-1,getdate())),GETDATE()+1)

Last date of Current Month

select dateadd(day,-datepart(day,getdate()),DATEADD(mm,1,getdate()))

First date of Current Year

 select cast(DATENAME(yy,getdate()) as DATE)

Last date of Current Year

 select dateadd(day,-1,dateadd(mm,12,cast(DATENAME(yy,getdate()) as DATE)))

Previous Month Last Date

select DATEADD(DAY, -day(DATEADD(mm,-1,getdate())),GETDATE())

Previous Month First Date

select dateadd(mm,-1,dateadd(day,1,DATEADD(DAY, -day(DATEADD(mm,-1,getdate())),GETDATE())))

---Other way that are alternate of above all

--Previous month first date

select dateadd(mm,datediff(mm,0,getdate())-1,0)

--Previous month last date

select dateadd(mm,datediff(mm,0,getdate()),-1)

--current year first date

select dateadd(yy,datediff(YY,0,getdate()),0)

--current year last date

select dateadd(yy,datediff(YY,0,getdate())+1,-1)

--current month last date

select dateadd(mm,datediff(mm,0,getdate())+1,-1)

--current month first date

select dateadd(mm,datediff(mm,0,getdate()),0)

Note : There are alternative ways also to fetch the same things differ from logic to logic as below

--First Date and Last Date of Year  you can use this logic also

 SELECT '01/01/'+cast(year(getdate()) as varchar(4)) as [First Day],

 '12/31/'+cast(year(getdate()) as varchar(4)) as [Last Day]

In order to achieve the same output in SSRS read my previous blog

 http://sqlandssrssolutions.blogspot.in/2013/10/first-and-last-date-of-current-month-or.html