CODEWITHSUNDEEP
mysqlsql
pufAmuf
pufAmuf

Reputation: 7805

Display 'closed' if IS_CLOSED is true

If is_closed is returned as true from a query, how do I tell the query to set closing_time to closed, instead of displaying what is really in the row... such as 23:00:00 ?

I tried

AND IF(is_closed = 1, 1, 0) closing_time = 'closed'

but that resulted in

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'closing_time = 'closed' GROUP BY venues.VENUE_NAME' at line 27

Thanks!

edit closing_time is a date field.

Upvotes: 0

Views: 100

Answers (2)

mechanical_meat
mechanical_meat

Reputation: 169484

In your field list add a conditional statement:

SELECT 
  < some column(s) here >, 
  CASE WHEN is_closed = 1 
       THEN 'closed' 
       ELSE closing_time END AS CLOSING_TIME_OR_CLOSED
...

A tested example may be found here: http://sqlfiddle.com/#!2/108c1/2

In contrast to the IF statement, the CASE statement is ANSI SQL meaning you can run your query on any RDBMS which implements the ANSI standard.

Upvotes: 2

spencer7593
spencer7593

Reputation: 108500

If closing_time is datatype TIME, then you could use an expression like this in your SELECT list: 

SELECT IF(is_closed=1,'closed',TIME_FORMAT(closing_time,'%H:%i')) AS closing_time

Note that this expression will return a string (a VARCHAR datatype).

Upvotes: 1

Related Questions