How to add days to the current date?

Question

I am trying to add days to the current date and it's working fine but when I add 360 days to the current date it gives me wrong value.

eg: Current Date is 11/04/2014

And I am adding 360 Days to it, it should give me 11/04/2015, but it is showing the same date 11/04/2014. the year is not changing.

Here is my code:

select dateadd(dd,360,getdate())

Why should it return `11/04/2015`? Correct would be `2015-04-06 15:00:00` and indeed i get this result. But you can add a year instead of 360 days: `select dateadd(year,1,getdate())` — Tim Schmelter, Apr 11 '14 at 13:00
If you do `select getdate(), dateadd(dd, 360, getdate())`, you get two identical values ? — Raphaël Althaus, Apr 11 '14 at 13:03
The value you're expecting (11/04/2015) is 365 days (a year) from today. — Knightwisp, Apr 11 '14 at 13:03
Way late to the party, I know, but this sounds a bit suspicious. The 360 vs 365 thing, for one. Another is that it's returning today's date. @RaphaëlAlthaus has a valid question. — jinglesthula, Jun 22 '17 at 14:17

score 62 · Answer 1 · edited Sep 04 '14 at 04:40

62

Just do-

Select (Getdate()+360) As MyDate

There is no need to use dateadd function for adding or subtracting days from a given date. For adding years, months, hours you need the dateadd function.

edited Sep 04 '14 at 04:40

Shell

6,818
11
39
70

answered Sep 04 '14 at 03:46

Shahid Mustafa

661
5
3

14

Poor code as it is not explicit. dateadd(dd,360,getdate()) makes it more obvious (and you rely on the internal implementation of a datetime), though this may be part of the SQL standard I didn't think it was. – CestLaGalere Oct 06 '15 at 17:52
4

DateAdd should be used instead of +/- operators on dates. http://stackoverflow.com/a/29696475/354756 – daniloquio Dec 02 '16 at 21:27

score 38 · Answer 2 · edited Oct 06 '16 at 22:25

38

select dateadd(dd,360,getdate()) will give you correct date as shown below:

2017-09-30 15:40:37.260

I just ran the query and checked:
Please check the attached image

edited Oct 06 '16 at 22:25

Jeff

12,555
5
33
60

answered Oct 05 '16 at 10:12

Mahantesh

429
6
13

score 28 · Answer 3 · edited Jul 30 '20 at 20:25

28

Dateadd(datepart,number,date)

You should use it like this:

select DATEADD(day,360,getdate())

Then you will find the same date but different year.

edited Jul 30 '20 at 20:25

Dale K

25,246
15
42
71

answered Sep 25 '16 at 09:19

TheChanger

381
3
3

score 12 · Answer 4 · answered Aug 07 '18 at 17:20

12

From the SQL Server 2017 official documentation:

SELECT DATEADD(day, 360, GETDATE());

If you would like to remove the time part of the GETDATE function, you can do:

SELECT DATEADD(day, 360, CAST(GETDATE() AS DATE));

answered Aug 07 '18 at 17:20

Dherik

17,757
11
115
164

score 2 · Answer 5 · edited Dec 09 '19 at 14:53

2

In SQL Server 2008 and above just do this:

SELECT DATEADD(day, 1, Getdate()) AS DateAdd;

edited Dec 09 '19 at 14:53

Ling Vu

4,740
5
24
45

answered Dec 09 '19 at 13:57

moamer khalefa

21
1

score 1 · Answer 6 · answered Sep 25 '16 at 09:31

1

can try this

select (CONVERT(VARCHAR(10),GETDATE()+360,110)) as Date_Result

answered Sep 25 '16 at 09:31

Asifuzzaman Redoy

1,773
1
15
30

DateAdd should be used instead of +/- operators on dates. http://stackoverflow.com/a/29696475/354756 – daniloquio Dec 02 '16 at 21:27

score 1 · Answer 7 · edited Jan 08 '18 at 16:23

Two or three ways (depends what you want), say we are at Current Date is (in tsql code) -

DECLARE @myCurrentDate datetime = '11Apr2014 10:02:25 AM'

(BTW - did you mean 11April2014 or 04Nov2014 in your original post? hard to tell, as datetime is culture biased. In Israel 11/04/2015 means 11April2014. I know in the USA 11/04/2014 it means 04Nov2014. tommatoes tomatos I guess)

SELECT @myCurrentDate + 360 - by default datetime calculations followed by + (some integer), just add that in days. So you would get 2015-04-06 10:02:25.000 - not exactly what you wanted, but rather just a ball park figure for a close date next year.
SELECT DateADD(DAY, 365, @myCurrentDate) or DateADD(dd, 365, @myCurrentDate) will give you '2015-04-11 10:02:25.000'. These two are syntatic sugar (exacly the same). This is what you wanted, I should think. But it's still wrong, because if the date was a "3 out of 4" year (say DECLARE @myCurrentDate datetime = '11Apr2011 10:02:25 AM') you would get '2012-04-10 10:02:25.000'. because 2012 had 366 days, remember? (29Feb2012 consumes an "extra" day. Almost every fourth year has 29Feb).
So what I think you meant was
```
SELECT DateADD(year, 1, @myCurrentDate)
```
which gives 2015-04-11 10:02:25.000.
or better yet
```
SELECT DateADD(year, 1, DateADD(day, DateDiff(day, 0, @myCurrentDate), 0))
```
which gives you 2015-04-11 00:00:00.000 (because datetime also has time, right?). Subtle, ah?

score 1 · Answer 8 · edited Jul 30 '20 at 20:24

1

Add Days in Date in SQL

 DECLARE @NEWDOB DATE=null
 
 SET @NEWDOB= (SELECT DOB, DATEADD(dd,45,DOB)AS NEWDOB FROM tbl_Employees)

edited Jul 30 '20 at 20:24

Dale K

25,246
15
42
71

answered Apr 06 '19 at 09:14

Code

679
5
9

score 1 · Answer 9 · edited Jul 30 '20 at 20:24

1

This will give total number of days including today in the current month.

select day(getDate())

edited Jul 30 '20 at 20:24

Dale K

25,246
15
42
71

answered Nov 27 '19 at 10:16

Rinku Choudhary

1,529
1
13
22

score -1 · Answer 10 · edited Aug 08 '18 at 22:47

-1

SELECT DateAdd(5,day(getdate()) this is for adding 5 days to current days. for eg:today date is 23/08/2018 it became 28/08/2018 by using the above query

edited Aug 08 '18 at 22:47

Baptiste Mille-Mathias

2,144
4
31
37

answered Aug 08 '18 at 17:34

Vimal_devs

7
1

How to add days to the current date?

10 Answers10

Linked

Related