September 29, 2015 at 10:00 am
Table contains Orderdate Column which contains roof year built years starting from 1991 to 2010. i need to get years where roof of year built is over 17 years old. I want to use mod function or any built in function or query. Please, can any body guide me
September 29, 2015 at 10:10 am
Is this homework?
What have you tried so far?
What would you use the mod function for?
Based upon the limited information you have provided, try this:
SELECT *
FROM YourTable
WHERE Orderdate >= DATEADD(yyyy, -17, getdate())
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 29, 2015 at 10:12 am
Can you post DDL, sample data and expected results? You can read how to do it in the link on my signature.
It doesn't seem like a difficult task, but I'm not sure that I understand it correctly. Please share what you have tried as well.
And this might help you as well: http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/
September 29, 2015 at 11:44 am
I tried your statement but, I am getting blank rows selection.
To be specific, I want to explain the question once again. The table consists of roofyearbuilt column which has data like 1991,1994,1993....2010. I want to specify a condition in WHERE clause which can give me the result as " roof is over 17 years old".
In my query I used a condition in Where clause as : " WHERE roofyearbuilt <= 1998".
I want to change this condition and specify an alternative there.
September 30, 2015 at 11:43 am
Try this:
WHERE roofyearbuilt <= YEAR(DATEADD(yy,-17,GETDATE()))
September 30, 2015 at 11:52 am
This statement also works. THANKS!
I also tried with the condition as : WHERE roofyearbuilt < year(getdate()) - 17. This works too.
September 30, 2015 at 2:58 pm
patilpallavi16 (9/30/2015)
This statement also works. THANKS!I also tried with the condition as : WHERE roofyearbuilt < year(getdate()) - 17. This works too.
Do you have any idea why these different statements work, and why they don't?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 1, 2015 at 2:38 am
Try the code below, always try to keep your code simple:
WHERE YEAR(roofyearbuilt) - 1991 > 17
October 1, 2015 at 3:12 am
itumelengd (10/1/2015)
Try the code below, always try to keep your code simple:WHERE YEAR(roofyearbuilt) - 1991 > 17
As per a different post, putting functions around columns in a where clause makes the query NONSARGABLE, it will scan the whole table/index.
The options already posted are much better than this in terms of performance as they will be SARGABLE as they are not running any functions against the column
October 1, 2015 at 6:12 am
I am still a student and learning. Can you tell me the answer plz.
October 1, 2015 at 6:17 am
patilpallavi16 (10/1/2015)
I am still a student and learning. Can you tell me the answer plz.
A number of answers have already been given above.
If these don't suit you needs please post DDL, data and expected outcomes as per the link on posting questions i my signature.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply