CODEWITHSUNDEEP
sql-serverdateadd
Fazil Mir
Fazil Mir

Reputation: 833

How to add days to the current date?

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

Upvotes: 60

Views: 328883

Answers (10)

Mahantesh
Mahantesh

Reputation: 439

select dateadd(dd,360,getdate())

will give you correct date as shown:

2017-09-30 15:40:37.260

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

Upvotes: 39

TheChanger
TheChanger

Reputation: 401

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.

Upvotes: 30

Code
Code

Reputation: 739

Add Days in Date in SQL

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

Upvotes: 1

Rinku Choudhary
Rinku Choudhary

Reputation: 2161

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

select day(getDate())

Upvotes: 1

moamer khalefa
moamer khalefa

Reputation: 21

In SQL Server 2008 and above just do this: 

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

Upvotes: 2

Vimal_devs
Vimal_devs

Reputation: 7

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

Upvotes: -1

Dherik
Dherik

Reputation: 19050

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

Upvotes: 12

LongChalk_Rotem_Meron
LongChalk_Rotem_Meron

Reputation: 803

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)

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

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

  3. So what I think you meant was 

    SELECT DateADD(year, 1, @myCurrentDate)

    which gives 2015-04-11 10:02:25.000.

  4. 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?

Upvotes: 1

Asifuzzaman
Asifuzzaman

Reputation: 1783

can try this 

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

Upvotes: 1

Shahid Mustafa
Shahid Mustafa

Reputation: 681

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.

Upvotes: 64

Related Questions