Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

add 10 years to getdate Expand / Collapse
Author
Message
Posted Tuesday, June 24, 2014 10:44 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 8:08 AM
Points: 744, Visits: 1,056
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
Post #1585586
Posted Tuesday, June 24, 2014 10:50 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:34 PM
Points: 20,676, Visits: 32,269
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())




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1585588
Posted Tuesday, June 24, 2014 11:02 AM This worked for the OP Answer marked as solution
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 3:37 AM
Points: 466, Visits: 609
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.
Post #1585595
Posted Tuesday, June 24, 2014 11:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:34 PM
Points: 12,923, Visits: 12,342
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1585598
Posted Tuesday, June 24, 2014 11:13 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 8:08 AM
Points: 744, Visits: 1,056
thanks
Post #1585604
Posted Tuesday, June 24, 2014 11:15 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 11:54 AM
Points: 3,971, Visits: 3,411
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
Post #1585605
Posted Tuesday, June 24, 2014 11:16 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:34 PM
Points: 20,676, Visits: 32,269
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1585606
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse