add 10 years to getdate

Question

add 10 years to getdate

river1

SSC-Insane

Points: 24309
More actions
June 24, 2014 at 10:44 am

#287858

Hi,
I want to add 10 years to the current date.
select getdate()
I made:
select getdate() +10 but it does not work.
Thank you

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

river1 (6/24/2014)
Hi,
I want to add 10 years to the current date.
select getdate()
I made:
select getdate() +10 but it does not work.
Thank you

Try:

dateadd(year,10,getdate())

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 2

SELECT DATEADD(YEAR,10,GETDATE()), This will add 10 Years to the current date.

For ref: http://msdn.microsoft.com/en-IN/library/ms186819.aspx

Moreover, the Query you provided I.e SELECT GETDATE()+10 will add 10 Days to current date.

Can anyone please explain this.?

Thanks in advance.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Mr. Kapsicum (6/24/2014)
Moreover, the Query you provided I.e SELECT GETDATE()+10 will add 10 Days to current date.
Can anyone please explain this.?
Thanks in advance.

You discovered one of those (un)documented features. When doing math against a datetime value it will use days. The correct way is to use DATEADD.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

river1 SSC-Insane Points: 24309 More actions · Answer 4

river1

SSC-Insane

Points: 24309

June 24, 2014 at 11:13 am

#1724413

thanks

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

It adds to the days because under the hood, a datetime is really two integers. The first integer is the number of days since 1/1/1753. The second integer is the number of 0.003 timeslices since midnight. When you add an integer to a date, it adds the number to the first integer, so you increment by N days. To play with this a little more, try this:

SELECT GETDATE() + GETDATE();

For a great list of date routines, see Lynn's post at http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/.

Sean is right - the right way to do it is to use DATEADD.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Sean Lange (6/24/2014)
Mr. Kapsicum (6/24/2014)
Moreover, the Query you provided I.e SELECT GETDATE()+10 will add 10 Days to current date.
Can anyone please explain this.?
Thanks in advance.
You discovered one of those (un)documented features. When doing math against a datetime value it will use days. The correct way is to use DATEADD.

It comes down to the underlying storage of the datetime value. You can add days by adding integer values to the function and it acts just as if you used DATEADD(DAY,10,GETDATE()). This doesn't work with new DATE or DATETIME2 data types.