December 5, 2012 at 9:34 am
How to specify a where clause in a view with between operator???
I have a view which shows empname and sal data and week
Results displayed by the view are
select empname,sal,week
from view test
EmpName Sal Week
A 100 07/02/2012
A 200 07/02/2011
B 500 07/02/2012
B 1000 07/02/2011
C 200 07/09/2012
C 300 07/23/2011
D 100 07/12/2010
Query 1
select empname,sal,week
from view test
where week='07/02/2012'
Results Displayed:
A 100 07/02/2012
B 500 07/02/2012
Query 2:
select empname,sal,week
from view test
where week='07/09/2012'
Result
C 200 07/09/2012
Query 3 :
select empname,sal,week
from view test
where week between '07/02/2012' and '07/16/2012'
the results to be displayed are:
A 100 07/02/2012
B 500 07/02/2012
C 200 07/09/2012
But it displays all years data for the month 07 between dates 02 and 16..
A 100 07/02/2012
A 200 07/02/2011
B 500 07/02/2012
B 1000 07/02/2011
C 200 07/09/2012
D 100 07/12/2010
any help on this?
December 5, 2012 at 10:01 am
Hi, what's the datatype of the Week field?
Thanks
December 5, 2012 at 11:08 am
var05 (12/5/2012)
any help on this?
Yeah, don't store dates as varchars, store them as datetime data types and then the comparisons will be date-based, not string based.
I strongly recommend fixing the data types. Any other form of workaround will be slow, painful or both.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 6, 2012 at 2:03 am
Thanks for ur reply..
Week is a calcualted feild from date and I had changed the string to date and it works fine. Thanks!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply