Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Find First and Last day of Current Month for Last Year from Current Date

In this puzzle you have to find the First and Last day of Current Month for Last Year from Current Date.

Please check out the sample input values and sample expected output below.

Sample Input

select getdate()

(No column name)
2017-02-16 12:01:44.343

Expected Output

LastYearFirstDate
02/01/16

LastYearLastDate
02/29/16

Script

Use below script to create table and insert sample data into it.

--


select getdate()

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution 1 – For SQL 2008+


--

DECLARE @Date AS DATE = GETDATE()
SELECT CONVERT(VARCHAR(8),DATEADD(month, DATEDIFF(month, 0, DATEADD(Year,-1,@Date)), 0),1) [LastYearFirstDate]
GO


DECLARE @Date AS DATE = GETDATE()
SELECT CONVERT(VARCHAR(8),EOMONTH(DATEADD(month, DATEDIFF(month, 0, DATEADD(Year,-1,@Date)), 0)),1) [LastYearLastDate]
GO

--

Solution 2 – For SQL 2012+


--


DECLARE @Date AS DATE = GETDATE()
SELECT FORMAT(DATEADD(month, DATEDIFF(month, 0, DATEADD(Year,-1,@Date)), 0),'MM/dd/yy') [LastYearFirstDate]
GO


DECLARE @Date AS DATE = GETDATE()
SELECT FORMAT(dateadd(s,-1,dateadd(mm,datediff(m,0,DATEADD(month, DATEDIFF(month, 0, DATEADD(Year,-1,@Date)), 0))+1,0)), 'MM/dd/yy') [LastYearLastDate]
GO

--

Solution 3


--


DECLARE @Date AS DATE = GETDATE()
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date) - 12, 0)
GO


DECLARE @Date AS DATE = GETDATE()
SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date) + 1 - 12, 0))
GO



--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com