Where clause with 2 columns - 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

General SQL Server Forums

New to SQL Server Programming

Where clause with 2 columns

Author

Topic

tibant
Starting Member

3 Posts

Posted - 2013-12-17 : 15:30:53

Hi!
I have a table and want to filter on 2 columns: (table indexed on c2 and c3)
---------------------------------------------
c1 c2 c3
aaa 1 1/2/2013
bbb 1 2/2/2013
ccc 1 3/2/2013
ddd 2 1/2/2013
--------------------------------------
select * from x where c2=1 and c3=1/2/2013 order by c3 - does not work.
can you give me a hint if the where clause wrong, or need special indexes??

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-12-17 : 15:33:44

Explain "does not work" for us.

You need to add single quotes around the date.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-12-18 : 05:30:06

select * from x where c2=1 and c3='1/2/2013' order by c3
Post us back the error message

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-18 : 06:28:43

quote:
Originally posted by tibant

Hi!
I have a table and want to filter on 2 columns: (table indexed on c2 and c3)
---------------------------------------------
c1 c2 c3
aaa 1 1/2/2013
bbb 1 2/2/2013
ccc 1 3/2/2013
ddd 2 1/2/2013
--------------------------------------
select * from x where c2=1 and c3=1/2/2013 order by c3 - does not work.
can you give me a hint if the where clause wrong, or need special indexes??

Thanks.

does c3 have timepart also? then you need this


select * from x where c2=1 and c3>='20130201'
and c3 < '20130202'

assuming your original date format being ddmmyyyy

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

tibant
Starting Member

3 Posts

Posted - 2013-12-20 : 18:19:41

Hi!

I have no error message, only no results, but I know there are matching records.

quote:
Originally posted by bandi

select * from x where c2=1 and c3='1/2/2013' order by c3
Post us back the error message

--
Chandu

tibant
Starting Member

3 Posts

Posted - 2013-12-20 : 18:30:41

Thanks. This (greater and smaller) also results zero lines.
Yes the column was date/time, but I also tried with another date only, and the same result. (This is an aspx page) If I program it, it will work: (leaving c3 out from the where)

select * from x where c2=1 order by c3
do while x.eof
if c3 = 1/2/2013 then
....
end if
loop

this way I have results ....
(this is just a wrong solution)

Thanks,

quote:
Originally posted by visakh16

quote:
Originally posted by tibant

Hi!
I have a table and want to filter on 2 columns: (table indexed on c2 and c3)
---------------------------------------------
c1 c2 c3
aaa 1 1/2/2013
bbb 1 2/2/2013
ccc 1 3/2/2013
ddd 2 1/2/2013
--------------------------------------
select * from x where c2=1 and c3=1/2/2013 order by c3 - does not work.
can you give me a hint if the where clause wrong, or need special indexes??

Thanks.

does c3 have timepart also? then you need this

select * from x where c2=1 and c3>='20130201'
and c3 < '20130202'

assuming your original date format being ddmmyyyy

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-21 : 01:10:51

does 1/2/2013 represent 1st Feb 2013 or 2nd Jan 2013 for you?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-12-24 : 07:20:02

If there is time part attached, consider using this

select * from x where c2=1 and c3>='20130201' and c3<'20130202' order by c3

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources