Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
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)

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

Related questions

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

28.4k questions

29.7k answers

500 comments

94.2k users

Browse Categories

...