Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Views -between operator in where clause Expand / Collapse
Author
Message
Posted Wednesday, December 5, 2012 9:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 1, 2014 8:41 AM
Points: 101, Visits: 437
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?
Post #1393085
Posted Wednesday, December 5, 2012 10:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:51 PM
Points: 2,357, Visits: 4,339
Hi, what's the datatype of the Week field?
Thanks
Post #1393106
Posted Wednesday, December 5, 2012 11:08 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 2:20 AM
Points: 42,493, Visits: 39,298
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
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1393137
Posted Thursday, December 6, 2012 2:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 1, 2014 8:41 AM
Points: 101, Visits: 437
Thanks for ur reply..

Week is a calcualted feild from date and I had changed the string to date and it works fine. Thanks!
Post #1393374
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse