Need help with CASE statement

Question

I have a column named TimeSlots that holds a twelve hour range of dates, 7:00:00 thru 19:00:00.  I have a stored procedure that accepts a varchar parameter that could be 'AM' or 'PM'.  Using a SELECT statement I am using a CASE statement inside the WHERE to get either TimeSlots < '12:00:00' for AM times or TimeSlots >= '12:00:00' for PM times.  It ain't working.

AND [TimeSlot] = CASE WHEN @ApptTime = 'AM' THEN [TimeSlot] < '12:00:00'
                                     WHEN @ApptTime = 'PM' THEN [TimeSlot] >= '12:00:00'

How can this be done?

Answer 1

AND 1 = CASE 
  WHEN @ApptTime = 'AM' AND [TimeSlot] < '12:00:00' THEN 1
  WHEN @ApptTime = 'PM' AND [TimeSlot] >= '12:00:00' THEN 1
  ELSE 0 END

Tom

Answer 2

is the TimeSlot is "varchar" data type ?

Answer 3

TimeSlot is time(0) data type

Answer 4

where (@ApptTime = 'AM' and [TimeSlot] < '12:00:00')
or (@ApptTime = 'PM' and [TimeSlot] > '12:00:00')

Answer 5

>>  I have a column named TimeSlots that holds a twelve hour range of dates, 07:00:00 thru 19:00:00. <<

This doesn't make any sense. You show hours, but you talk about dates. And you didn't bother to post any DDL or sample data. I hope your boss doesn't make you work from specs like this.

>>  I have a stored procedure that accepts a VARCHAR(n) parameter that could be 'AM' or 'PM'.  Using a SELECT statement I am using a CASE statement [sic] is inside the WHERE to get either TimeSlots < '12:00:00' for AM times or TimeSlots >= '12:00:00' for PM times.  It ain't working. <<

CASE is not a statement; it's an expression. You've never read any of the ISO-8601 specs for date and time, so you're using your old local dialect. This kind of display formatting should not be done in the database at all. Clean up your data before you invoke your procedure. If for some reason you're dealing with a bunch of people who don't know how to tell time, then you can add the old am/pm designations and convert the hours to's or whatever they want.

Oh, the THEN clauses in a CASE expression are where the values go, not predicates. You also think that this predicate replaces the required start and stop times that define a temporal interval. SQL Server does not have the ANSI/ISO interval data types. Yet. 

AND local_dialect_flag 
       = CASE WHEN @ApptTime = 'AM' 
                    THEN [TimeSlot] < '12:00:00'
                     WHEN @ApptTime = 'PM' 
                     THEN [TimeSlot] >= '12:00:00'

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL