Year query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Year query

Author

Topic

ann
Posting Yak Master

220 Posts

Posted - 2007-04-23 : 10:18:37

I have to create a query whereby I need to extract info if the year of a date field(TerminateYear) is not <= than a parameter year or if it's null.
Example:
@year = 2007
ID, TerminateYear
1, Null
2, 12/15/2006
3, 1/20/2005
4, 2/10/2008

Results:
1, Null
4, 2/10/2008

Hope this is enough information --- thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-23 : 10:21:41

[code]Select *
from Table
Where (Year(TerminateYear) > @year or TerminateYear IS NULL)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-23 : 10:23:03

[code]
select ID, TerminateYear
from yourtable
where TerminateYear >= dateadd(year, @year - 1900 + 1, 0)
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-23 : 10:23:53

Harsh-bot beat me to it

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-23 : 10:25:43

quote:

Originally posted by khtan


select ID, TerminateYear
from   yourtable
where  TerminateYear >= dateadd(year, @year - 1900 + 1, 0)

Much better performance-wise! Excellent KH...Nice to know something new from you, always !

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-23 : 14:05:39

quote:

Originally posted by khtan


select ID, TerminateYear
from   yourtable
where  TerminateYear >= dateadd(year, @year - 1900 + 1, 0) or TerminateYear IS NULL

Sorry KH, I just didn't want the original poster to think that was the complete solution. But, kudos for being for efficient. :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-23 : 17:55:23

Thanks Lamprey. I missed that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources