Calculate 90 days and 3 Years agon from an Effective Date in a table?

Question

Calculate 90 days and 3 Years agon from an Effective Date in a table?

Welsh Corgi

SSC Guru

Points: 116520
More actions
September 30, 2014 at 7:02 am

#397431

What would be the most straight forword to Calculate 90 days and 3 Years ago from an Effective Date in a table?
as in
SELECT EffectiveDate
from FL.CEFHistory
I need to return the effective date - 90 days and 1 year from that.
Any feedback would be greatly appreciated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

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

LinksUp SSCertifiable Points: 6526 More actions · Answer 1

Welsh Corgi (9/30/2014)
What would be the most straight forword to Calculate 90 days and 3 Years ago from an Effective Date in a table?
I need to return the effective date - 90 days and 1 year from that.

Not sure which one you want, 3 years and 90 days ago or 1 year and 90 days ago. Either way, you can adjust the following to fit your situation:

select EffectiveDate, DATEADD(dd, -90, DATEADD(yy, -1, EffectiveDate))

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

So how would I do it if I select the column from the table?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Xedni SSCertifiable Points: 6951 More actions · Answer 3

Use LinksUp code, just add the from clause that you supplied in your OP

select EffectiveDate, DATEADD(dd, -90, DATEADD(yy, -1, EffectiveDate))

from FL.CEFHistory

Executive Junior Cowboy Developer, Esq.[/url]

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

That's awesome. Thank you so much.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

I know you've seen this before, but I guess is merits posting. Lynn has a page of date calculations that can be used to get you to almost any date relative to today at http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/. I would start there if you want to learn how to use date math efficiently.

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Ed Wagner (10/1/2014)
I know you've seen this before, but I guess is merits posting. Lynn has a page of date calculations that can be used to get you to almost any date relative to today at http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/. I would start there if you want to learn how to use date math efficiently.

Thank you Sir and thank you Lynn for all that you do.:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/