sql – How to get the first and last date of the current year?

sql – How to get the first and last date of the current year?

SELECT
   DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS StartOfYear,
   DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1) AS EndOfYear

The above query gives a datetime value for midnight at the beginning of December 31. This is about 24 hours short of the last moment of the year. If you want to include time that might occur on December 31, then you should compare to the first of the next year, with a < comparison. Or you can compare to the last few milliseconds of the current year, but this still leaves a gap if you are using something other than DATETIME (such as DATETIME2):

SELECT
   DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS StartOfYear,
   DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1) AS LastDayOfYear,
   DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, 0) AS FirstOfNextYear,
   DATEADD(ms, -3, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, 0)) AS LastTimeOfYear

Other Periods

This approach has two nice aspects: good performance and it can easily be changed for other periods by replacing both occurrences of yy (=year) with a different string:

yy, yyyy    year
qq, q       quarter
mm, m       month
wk, ww      week 

(Be careful of weeks: the starting day depends on server settings.)

Tech Details

This works by figuring out the number of years since 1900 with DATEDIFF(yy, 0, GETDATE()) and then adding that to a date of zero = Jan 1, 1900. This can be changed to work for an arbitrary date by replacing the GETDATE() portion or an arbitrary year by replacing the DATEDIFF(...) function with Year – 1900.

 SELECT
   DATEADD(yy, DATEDIFF(yy, 0, 20150301), 0) AS StartOfYearForMarch2015,
   DATEADD(yy, 2015 - 1900, 0) AS StartOfYearFor2015

Heres a fairly simple way;

SELECT DATEFROMPARTS(YEAR(GETDATE()), 1, 1) AS First Day of Current Year;
SELECT DATEFROMPARTS(YEAR(GETDATE()), 12, 31) AS End of Current Year;

Its not sexy, but it works.

sql – How to get the first and last date of the current year?

You can get the current year using DATEPART function, from the current date obtained using getUTCDate()

SELECT 
    01/01/ + CONVERT(VARCHAR(4), DATEPART(yy, getUTCDate())), 
    31/12/ + CONVERT(VARCHAR(4), DATEPART(yy, getUTCDate()))

Leave a Reply

Your email address will not be published.