Using multiple dates in WHERE clause

[hertelt]

I have a multiple select box in a form. I am trying to select records in SQL using the list that this form field produces. They are dateTime values. How do I use this list in the WHERE clause of my select statement?

[Dan_Bracuk]

Generally the sql syntax is

where somefield in (value1, value2, etc)

Also, cfqueryparam has a list atribute that does nice things for you.

But if you are sending datetime values, you will have to get match right down to the second. That may or may not create a logic problem for you.

[MichaelSJudd]

Man, I really hate posting anywhere near Dan - I've been watching his mastery for years. (Talk about the big leagues) But here goes anyway...

I've gotten my little behind kicked by more than a view date issues, and I have basically gotten away from exact matches (unless you really need them).

I'd probably do something like this:

SELECT field1,field2...
WHERE datetime_column_in_db > (OR LTE,OR GT/LT) '#datetimeformatyoulike(form.datetimevalue1)#

Basically, finding records in RELATION to the form's datetime value has always been much easier for me.

And God (and Dan) have mercy on my soul...

[MichaelSJudd]

And obviously don't forget your "FROM" statement (like I did) :-)

[MichaelSJudd]

And, for multiple dates:

WHERE datetime_column_in_db > '#datetimeformatyoulike(form.datetimevalue1)# OR datetime_column_in_db > '#datetimeformatyoulike(formdatetimevalue2)# (ETC...)