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.
| Author |
Topic |
|
Mikehjun
Starting Member
24 Posts |
 Posted - 2011-12-20 : 14:43:20
|
| All I need is to select work requests created on this year.SELECT *FROM atbl_GISRequestsWHERE CreateDate >= YEAR((GETDATE()))However this query returns everything, any idea? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-12-20 : 14:59:14
|
You are comparing a number (year) to a date. Compare only the years. Either of the following should work:year(createdate) = year(getdate()) or DATEDIFF(YEAR,createdate,GETDATE()) = 0 |
 |
|
|
Mikehjun
Starting Member
24 Posts |
Posted - 2011-12-20 : 16:06:18
|
| Thank you so much! it works! you are exactly right! |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2011-12-21 : 03:17:33
|
| orcreatedate>=dateadd(year,datediff(year,0,getdate()),0) andcreatedate<dateadd(year,datediff(year,0,getdate())+1,0) MadhivananFailing to plan is Planning to fail |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-12-21 : 07:00:57
|
| Madhivanan, can I infer that even though your query looks longer, the reason you are doing it the way you are, is because if there is an index on createdate, your approach would allow that index to be used?The query I posted uses functions on the column values, so it could not make use of the index if there was one.Or, is it to avoid the per-row function evaluation that would be required in my approach? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2011-12-21 : 09:01:12
|
Yes I wrote it considering mostly on index part  MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
select current date in where clause