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

Jamie F · Accepted Answer · 2021-03-16T12:09:22.010

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

score 25 · Answer 2 · answered Oct 19 '18 at 20:50

25

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.

answered Oct 19 '18 at 20:50

Aubrey Love

552
4
9

2

Only available starting from Sql Server 2012. – Lukas Nov 24 '18 at 14:54
1

This is the simplest and most effective way. And most obvious. – darlove Oct 08 '20 at 14:12

Vikdor · Answer 3 · 2012-11-18T04:15:45.893

12

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

edited Nov 18 '12 at 04:15

answered Nov 18 '12 at 04:07

Vikdor

22,825
9
55
81

1

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. – Jamie F Nov 18 '12 at 04:09
convert the strings to `DATETIME` using `CONVERT` function, the dates should be in MM/dd/yyyy format. – Vikdor Nov 18 '12 at 04:10
2

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' – RandyMorris Nov 18 '12 at 04:11
1

@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. – Vikdor Nov 18 '12 at 04:15
1

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. – Damien_The_Unbeliever Nov 18 '12 at 07:22
1

@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. – Vikdor Nov 18 '12 at 08:15

score 7 · Answer 4 · answered Nov 11 '14 at 10:19

7

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

answered Nov 11 '14 at 10:19

Pradeep atkari

481
1
6
14

score 4 · Answer 5 · answered Nov 18 '12 at 09:22

4

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]

answered Nov 18 '12 at 09:22

Rahul Tripathi

152,732
28
233
299

1

This is not necessarily true, as there are many [falsehoods programmers believe about time](https://infiniteundo.com/post/25509354022/more-falsehoods-programmers-believe-about-time). – krillgar Jul 23 '18 at 12:08
Could anyone give an example of when this will not work? – slayernoah Mar 14 '19 at 16:59
@slayernoah Depending on the localization settings of the server, I think this will sometimes generate an error. – Jamie F Jul 02 '19 at 12:58
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). – darlove Oct 08 '20 at 14:17

score 3 · Answer 6 · edited May 11 '18 at 14:17

3

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

edited May 11 '18 at 14:17

Syscall

16,959
9
22
41

answered May 11 '18 at 14:15

Gayle

31
1

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; – bormat May 25 '18 at 14:15

score 2 · Answer 7 · answered Jun 12 '18 at 09:38

2

Another way: (Since SQL Server 2012)

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

answered Jun 12 '18 at 09:38

EstevaoLuis

1,902
7
28
35

score 1 · Answer 8 · edited Feb 13 '17 at 14:50

1

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

edited Feb 13 '17 at 14:50

Paul Roub

35,100
27
72
83

answered Feb 13 '17 at 14:47

DHANA LAKSHMI

11
1

Milod · Answer 9 · 2020-09-26T16:10:50.190

1

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.

edited Sep 26 '20 at 16:10

answered Sep 21 '20 at 08:16

Milod

21
6

Please provide explanation to your answer instead of just code. – kk. Sep 21 '20 at 08:41

score 0 · Answer 10 · answered Nov 18 '12 at 09:03

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

score 0 · Answer 11 · edited Feb 13 '17 at 13:43

0

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

edited Feb 13 '17 at 13:43

FelixSFD

5,456
10
40
106

answered Feb 13 '17 at 13:42

DHANA LAKSHMI

11
1

score 0 · Answer 12 · edited Jun 01 '17 at 20:13

0

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

edited Jun 01 '17 at 20:13

Bugs

4,356
9
30
39

answered Jun 01 '17 at 20:11

zadeveloper

56
1

You should explain your code. Code dumps are often downvoted and may be deleted. – Bugs Jun 01 '17 at 20:13

score 0 · Answer 13 · answered Apr 11 '18 at 12:38

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

score 0 · Answer 14 · edited Feb 25 '21 at 02:06

0

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

edited Feb 25 '21 at 02:06

Ran Marciano

1,254
5
7
25

answered Feb 24 '21 at 07:52

Divy Mistry

1

score -1 · Answer 15 · edited Aug 25 '14 at 20:45

-1

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

edited Aug 25 '14 at 20:45

guest

5,790
23
39

answered Aug 25 '14 at 20:24

R-Dubz

1

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 – Andrew Failor Nov 09 '18 at 19:37

score -1 · Answer 16 · edited Dec 21 '18 at 11:10

-1

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

edited Dec 21 '18 at 11:10

Miraj50

3,823
1
15
30

answered Dec 20 '18 at 15:58

Nathan Lincoln

11
2

score -2 · Answer 17 · edited Feb 13 '17 at 13:51

-2

Try this:

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

edited Feb 13 '17 at 13:51

Andy

43,170
54
150
214

answered Jan 12 '16 at 10:23

Ivan

87
1
2

Microsoft SQL Server. `Msg 195, Level 15, State 10, Line 1 'NOW' is not a recognized function name.` – jumxozizi Aug 22 '16 at 13:37
This answer uses MySQL functions (and casts dates to strings, which is one of my pet peeves). – Álvaro González Nov 28 '16 at 13:20

Soundararajan · Answer 18 · 2018-04-29T11:22:01.830

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.

score -3 · Answer 19 · edited Mar 19 '14 at 12:07

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

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

19 Answers19

Linked