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

Gopal

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

Jamie F
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

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

how to get first and last date of the current year javascript

Get date object for the first/last day of the current year

How to get a year first date and last date in javascript

How to get last three months first date and last date based on current date including current date in java?

How to get the first and last date of the month with Year and Month in python

How to get first and last date of a given week number and year?

Get first date of current year in Amazon Redshift

How do i get first date and Last date from month and year |Excel |Used Spinner for Month and Year|

Get first and last date from week and year

How to get current month last and first date in nodejs

How to get financial year for last two year in C# based on current date

How to get the first date and last date of current quarter in java.util.Date

How to get a specific date in the current year

How to get current date, month, year in scala

How to get current date in year type

How to get the Current Year to Current date in sql server 2008

How to get the last Sunday before current date?

Get first and last date of current month with JavaScript or jQuery

How do i get the last digit of the current year in PHP

Get First And Last Day Of Year

How to get the date of the first and last day of a week

How to get the first and the last date in a file

How to get current year?

how to get the first and last day of each month in a year?

How to get Last WeekNumber when week is first week of the year in SQL?

How to get last month previous date from current date in java

R/ Shiny - How to get current year with Sys.Date()?

How to get age by subtracting current year from date of birth in MySql

How to get current week number for that year from a date in MySQL?