Explore Courses Blog Tutorials Interview Questions
0 votes
in SQL by (20.3k points)

Using SQL Server 2000, how can I get the first and last date of the current year?

Expected Output:

01/01/2012 and 31/12/2012

1 Answer

0 votes
by (40.4k points)



 DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS StartOfYear,

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

The below query will give you a datetime value for midnight at the beginning of December 31. This is about 24 hours short of the last moment of the year. 

But, if you want to include the time that might occur on December 31, then you need to compare to the first of the next year, with a < comparison. Otherwise, you should compare to the last few milliseconds of the current year, but that will leave a gap if you are trying to use something other than DATETIME (such as DATETIME2) try this:


   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

Tech Details is as follows:

This can work by figuring out the number of years since 1900 with DATEDIFF(yy, 0, GETDATE()) and then adding that to date of zero = Jan 1, 1900. 

This will change to work for an arbitrary date by replacing the GETDATE() portion of an arbitrary year by replacing the DATEDIFF(...) function with "Year - 1900."


   DATEADD(yy, DATEDIFF(yy, 0, '20150301'), 0) AS StartOfYearForMarch2015,

   DATEADD(yy, 2015 - 1900, 0) AS StartOfYearFor2015

Related questions

Welcome to Intellipaat Community. Get your technical queries answered by top developers!

28.4k questions

29.7k answers


94.2k users

Browse Categories