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

Kornher

Answer 1

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

Spy

I know 0 represents 01/01/1900 - so DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) makes sense. But how about -1? DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1), what is -1 represent? is it represent the day count since 01/01/1900 - which in this case -1 becomes 31/12/1899? – Potency

@Sam: the underlying representation for DateTime is based on a whole number equals one day. So yes, -1 represents a day before 1900-01-01 (0) which is 1899-12-31. – Spy

Answer 2

Here's 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';

It's not sexy, but it works.

Aboutship

Only available starting from Sql Server 2012. – Seaware

Answer 3

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()))

Huge

This outputs strings, not dates. If that's what you really need, yay, but if you can use either, keep the dates as dates, and don't use them as strings. – Spy

convert the strings to DATETIME using CONVERT function, the dates should be in MM/dd/yyyy format. – Huge

I disagree, OP did not specify and the data formet set in sql server might return something like '2012-01-01 12:13:14' instead of '01/01/2012' – Chlorella

@RandyMorris, agree, the OP was asking if he can get DATETIME instead of string and obviously that may not match the expected output as per the question. – Huge

Why would you use an ambiguous date format (and the strings in your answer don't match the format in your comment)? yyyyMMdd is unambiguous. – Pavlov

@Damien_The_Unbeliever, the query in the response is to match the expected output as per the question. The comment about DATETIME is a response to OP's comment about getting date instead of string. The answer doesn't reflect this discussion. – Huge

Answer 4

Another way: (Since SQL Server 2012)

SELECT
    DATEFROMPARTS(YEAR(GETDATE()), 1, 1) FirstDay,
    DATEFROMPARTS(YEAR(GETDATE()),12,31) LastDay

Swarm

Answer 5

simply write:-

select convert (date,DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE()),0))

start date of the year.

select convert (date,DATEADD(YEAR, DATEDIFF(YEAR,0,GETDATE()) + 1, -1))  

Grossularite

Answer 6

Every year has the 1 st as First date and 31 as the last date what you have to do is only attach the year to that day and month for example:-

 SELECT '01/01/'+cast(year(getdate()) as varchar(4)) as [First Day],
 '12/31/'+cast(year(getdate()) as varchar(4)) as [Last Day]

Dittany

This is not necessarily true, as there are many falsehoods programmers believe about time. – Ingesta

Could anyone give an example of when this will not work? – Sowell

@Sowell Depending on the localization settings of the server, I think this will sometimes generate an error. – Spy

Using this on SQL Server might not be safe depending on the regional settings. A safe version of this is to use the ISO format for dates that does always get correctly recognized regardless of the settings. This format for dates is yyyy-MM-dd. On top of that, the above returns strings, not real dates. The simplest way to do this kind of manipulation is datefromparts(year(getdate()), 1, 1) and datefromparts(year(getdate()), 12, 31). – Haggard

Answer 7

To get the first and the last day of the year, one can use the CONCAT function. The resulting value may be cast to any type.

CONCAT(YEAR(Getdate()),'-01-01') FirstOfYear,
CONCAT(YEAR(GETDATE()),'-12-31') LastOfYear

Adulation

Year does not work for me but date_part works start_year_date := CONCAT(date_part('Y',start_date_p)::int::text,'-01-01')::date; – Metronymic

Answer 8

The best way is to extract the current year then use concatenation like this :

SELECT CONCAT(year(now()), '-01-01') as start, -- fist day of current year
       CONCAT(year(now()), '-31-12') as end;   -- last day of current year

That gives you : start : 2020-01-01 and end : 2020-31-12 in date format.

Stowell

Please provide explanation to your answer instead of just code. – Zarf

I added a short but concise description ! – Stowell

Returns strings, not DateTime objects, so not too efficient, if the result needs to be change into a date. This also looks like a duplicate of an earlier answer. – Spy

Answer 9

For start date of current year:

SELECT DATEADD(DD,-DATEPART(DY,GETDATE())+1,GETDATE())

For end date of current year:

SELECT DATEADD(DD,-1,DATEADD(YY,DATEDIFF(YY,0,GETDATE())+1,0))

Chemar

Answer 10

The best way to get First Date and Last Date of a year Is

SELECT CAST(CAST(YEAR(DATEADD(YEAR,-1,GETDATE())) AS VARCHAR) + '-' + '01' + '-' + '01' AS DATE) FIRST_DATE

SELECT CAST(CAST(YEAR(DATEADD(YEAR,-1,GETDATE())) AS VARCHAR) + '-' + '12' + '-' + '31' AS DATE) LAST_DATE

Peltry

Answer 11

Check out this one:

select convert(varchar(12),(DateAdd(month,(Month(getdate())-1) * -1, DateAdd(Day,(Day(getdate())-1) * -1,getdate()))),103) as StartYear,
       convert(varchar(12),DateAdd(month,12 - Month(getdate()), DateAdd(Day,(31 - Day(getdate())),getdate())),103) as EndYear

Tootsy

Answer 12

SELECT DATEADD(DD,-DATEPART(DY,GETDATE())+1,GETDATE())

Chemar

Answer 13

print Cast('1/1/' + cast(datepart(yyyy, getdate()) as nvarchar(4)) as date)

Sodalite

You should explain your code. Code dumps are often downvoted and may be deleted. – Giuditta

Answer 14

It looks like you are interesting in performing an operation everything for a given year, if this is indeed the case, I would recommend to use the YEAR() function like this:

SELECT * FROM `table` WHERE YEAR(date_column) = '2012';

The same goes for DAY() and MONTH(). They are also available for MySQL/MariaDB variants and was introduced in SQL Server 2008 (so not for specific 2000).

Arbitral

Answer 15

In Microsoft SQL Server (T-SQL) this can be done as follows

--beginning of year
select '01/01/' + LTRIM(STR(YEAR(CURRENT_TIMESTAMP)))

--end of year
select '12/31/' + LTRIM(STR(YEAR(CURRENT_TIMESTAMP)))

CURRENT_TIMESTAMP - returns the sql server date at the time of execution of the query.

YEAR - gets the year part of the current time stamp.

STR , LTRIM - these two functions are applied so that we can convert this into a varchar that can be concatinated with our desired prefix (in this case it's either first date of the year or the last date of the year). For whatever reason the result generated by the YEAR function has prefixing spaces. To fix them we use the LTRIM function which is left trim.

Suffragette

Answer 16

If it reaches the 1st of Jan you might it to be still last years date.

select
convert(date, DATEADD(yy, DATEDIFF(yy, 0,  DATEadd(day, -1,getdate())), 0), 103 ) AS StartOfYear,
convert(date, DATEADD(yy, DATEDIFF(yy, 0, DATEDIFF(day, -1,getdate()))+1, -1), 103 )AS EndOfYear

Stephan

Answer 17

select to_date(substr(sysdate,1, 4) || '01/01'), to_date(substr(sysdate,1, 4) || '12/31') 
from dual

Laynelayney

This is for PL-SQL, post says SQL Server 2000, so you have to use T-SQL. To_Date and Sysdate don't exist in T-SQL – Jarret

Answer 18

Try this:

DATE_FORMAT(NOW(),'01/01/%Y')
DATE_FORMAT(NOW(),'31/12/%Y')

Linter

Microsoft SQL Server. Msg 195, Level 15, State 10, Line 1 'NOW' is not a recognized function name. – Poplin

This answer uses MySQL functions (and casts dates to strings, which is one of my pet peeves). – Vascular

Answer 19

---Lalmuni Demos---

create table Users
(
userid int,date_of_birth date
)

---insert values---

insert into Users values(4,'9/10/1991')

select DATEDIFF(year,date_of_birth, getdate()) - (CASE WHEN (DATEADD(year, DATEDIFF(year,date_of_birth, getdate()),date_of_birth)) > getdate() THEN 1 ELSE 0 END) as Years, 
MONTH(getdate() - (DATEADD(year, DATEDIFF(year, date_of_birth, getdate()), date_of_birth))) - 1 as Months, 
DAY(getdate() - (DATEADD(year, DATEDIFF(year,date_of_birth, getdate()), date_of_birth))) - 1 as Days,
from users

Alcoholize