CODEWITHSUNDEEP
mysqlsql
Ted
Ted

Reputation: 3875

how to simply return a column if some condition is true?

I have a table named A:

DATETIME_START || DATETIME_END || VALUE 
 --------------------------------------- 
2012-07-23     || 2013-07-23   ||  coffee  
2011-13-11     || 2013-01-09   ||  airport

all I am trying to do is - if the time of request is between DATETIME_START and DATETIME_END, simply return VALUE.

I tried:

SELECT 
IF (NOW() BETWEEN A.DATETIME_START AND A.DATETIME_END, VALUE,NULL)
FROM A

but what happens is that I get the string "IF (NOW() BETWEEN A.DATETIME_START AND A.DATETIME_END, VALUE,NULL)" in the result!

(I don't want to put the condition in the WHERE)

Why is that?

Upvotes: 1

Views: 140

Answers (3)

Mark
Mark

Reputation: 1093

The column name will be whatever you actually select. Say your select had looked like this instead:

SELECT
    IF (NOW() BETWEEN A.DATETIME_START AND A.DATETIME_END, ColumnA, ColumnB)

What should the resultset column name be when you are potentially getting results from 2 columns?

You may not even be selecting from any columns:

SELECT
    IF(0 < 1, 'True', 'False')

In your particular case you didn't really need an IF statement, but when you do have e.g. conditions or functions in your SELECT, you can use Alias to determine the column name yourself.

SELECT
    IF(0 < 1, 'True', 'False') AS YourName
  # or
    IF(0 < 1, 'True', 'False') AS `Your name with spaces`

Upvotes: 2

Vikdor
Vikdor

Reputation: 24124

Are you looking for this:

SELECT
    CASE 
        WHEN NOW() BETWEEN A.DATETIME_START AND A.DATETIME_END THEN VALUE
        ELSE NULL
    END
FROM A

Upvotes: 2

xdazz
xdazz

Reputation: 160853

SELECT VALUE FROM A
WHERE NOW() BETWEEN A.DATETIME_START AND A.DATETIME_END

Upvotes: 2

Related Questions