Feeds:
Posts
Comments

Posts Tagged ‘How to get First and Last day of the Year Using FORMAT function’

Recently, I came across a query where I needed to calculate the first and last day of the year. Fortunately, we do have the solution using DATEADD & DATEDIFF function. But the problem is we need to use these functions multiple times to achieve the first and last day of the year. So I went to the drawing board and started reducing the formula and finally I succeeded. Given below is the script to calculate the first and last day of the year using Format function (shipped with SQL Server 2012).

--This script is compatible with SQL Server 2012 and above.
SELECT DATEADD(YEAR,-1,FORMAT(Getdate(),'yyyy')) As [Result]
, 'First Day of Previous Year' As [Type]

UNION ALL
SELECT DATEADD(ms,-3,FORMAT(GETDATE(),'yyyy'))
, 'Last Day of Previous Year'

UNION ALL
SELECT FORMAT(GETDATE(),'yyyy')
, 'First Day of Current Year'

UNION ALL
SELECT DATEADD(ms,-3,FORMAT(DATEADD(YEAR,1,GETDATE()),'yyyy'))
, 'Last Day of Current Year'

UNION ALL
SELECT DATEADD(YEAR,1,FORMAT(Getdate(),'yyyy'))
, 'First Day of Previous Year'

UNION ALL
SELECT DATEADD(ms,-3,FORMAT(DATEADD(YEAR,2,GETDATE()),'yyyy'))
, 'Last Day of Previous Year'
--OUTPUT

first_day_and_last_day_of_year.1.1

Advertisements

Read Full Post »