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

Question

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

Answer 1

Query:

SELECT

 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:

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

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."

 SELECT

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

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