SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Where clause with 2 columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tibant
Starting Member

3 Posts

Posted - 12/17/2013 :  15:30:53  Show Profile  Reply with Quote
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

USA
36984 Posts

Posted - 12/17/2013 :  15:33:44  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 12/18/2013 :  05:30:06  Show Profile  Reply with Quote
select * from x where c2=1 and c3='1/2/2013' order by c3
Post us back the error message

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/18/2013 :  06:28:43  Show Profile  Reply with Quote
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
Go to Top of Page

tibant
Starting Member

3 Posts

Posted - 12/20/2013 :  18:19:41  Show Profile  Reply with Quote
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

Go to Top of Page

tibant
Starting Member

3 Posts

Posted - 12/20/2013 :  18:30:41  Show Profile  Reply with Quote
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


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/21/2013 :  01:10:51  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22760 Posts

Posted - 12/24/2013 :  07:20:02  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000