September 1, 2009 at 11:58 am
I have a column called Eff_date. Its a datetime format.
In my where clause I am comparing some thing like
WHERE cast(year(EFF_DT) as varchar) + cast(month(EFF_DT) as varchar) = '200912'
Its working perfect.
WHERE cast(year(EFF_DT) as varchar) + cast(month(EFF_DT) as varchar) = '200909'
Its not working. Because cast(month(EFF_DT) as varchar) returning 9 insted of 09. How can I generate 09 here ?
Thank for great help
Srini
September 1, 2009 at 12:08 pm
Well, you could use:
WHERE cast(year(EFF_DT) as varchar) + right('0' + cast(month(EFF_DT) as varchar), 2) = '200909'
But do keep in mind that what you're doing often kills query performance, because using functions in the Where clause pretty much guarantees it can't do an index seek, but will have to do a scan instead.
A better solution, performance-wise, would look like:
WHERE EFF_DT >= '2009-09-01' and EFF_DT < '2009-10-01'
That'll allow much more efficient index use.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 1, 2009 at 12:21 pm
Thanks For Great Help
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply