why this query is not working despite converting it to mysql

Question

I have a mssql query which i want to convert it into mySql, after converting still the query is not working.

here is my mssql query(original)

SELECT 
    product_id, 
    NOW() AS `current_date`,
    `bt`.`date_from` AS `starts_on`,
    `bt`.`date_end` AS `ends_on`,
    IF(`bt`.`end` >= NOW(),
         DATEDIFF(`bt`.`date_end`, NOW()), #show days until event ends
         0 #the event has already passed
    ) AS `days_remaining`

FROM `bookings` AS `bt`

here is my converted query (converted here:http://www.sqlines.com/online) :

SELECT 
    product_id, 
    NOW() AS `current_date`,
    `bt`.`date_from` AS `starts_on`,
    `bt`.`date_end` AS `ends_on`,
    CASE WHEN(`bt`.`end` >= NOW() THEN 
         DATEDIFF(`bt`.`date_end`, NOW()) ELSE  #show days until event ends
         0 #the event has already passed
    ) AS `days_remaining`

FROM `bookings` AS `bt`

but this convertd query gives the following error

Static analysis:

27 errors were found during analysis.

An expression was expected. (near "CASE" at position 154)
Unrecognized keyword. (near "CASE" at position 154)
Unrecognized keyword. (near "WHEN" at position 159)
Unexpected token. (near "(" at position 163)
Unexpected token. (near "`tn`" at position 164)
Unexpected token. (near "." at position 168)
Unexpected token. (near "`end`" at position 169)
Unexpected token. (near ">=" at position 175)
Unrecognized keyword. (near "NOW" at position 178)
Unexpected token. (near "(" at position 181)
Unexpected token. (near ")" at position 182)
Unrecognized keyword. (near "THEN" at position 184)
Unrecognized keyword. (near "DATEDIFF" at position 204)
Unexpected token. (near "(" at position 212)
Unexpected token. (near "`bt`" at position 213)
Unexpected token. (near "." at position 217)
Unexpected token. (near "`date_end`" at position 218)
Unexpected token. (near "," at position 228)
Unrecognized keyword. (near "NOW" at position 230)
Unexpected token. (near "(" at position 233)
Unexpected token. (near ")" at position 234)
Unexpected token. (near ")" at position 235)
Unrecognized keyword. (near "ELSE" at position 237)
Unexpected token. (near "0" at position 256)
Unexpected token. (near ")" at position 266)
Unrecognized keyword. (near "AS" at position 268)
Unexpected token. (near "`days_remaining`" at position 271)
SQL query: Documentation

SELECT product_id, NOW() AS `current_date`, `bt`.`date_from` AS `starts_on`, `bt`.`date_end` AS `ends_on`, CASE WHEN(`tn`.`end` >= NOW() THEN DATEDIFF(`bt`.`date_end`, NOW()) ELSE 0 ) AS `days_remaining` FROM `bookings` AS `bt` LIMIT 0, 25

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'THEN 
             DATEDIFF(`bt`.`date_end`, NOW()) ELSE               0       ' at line 6

here is my booking table structure

please see Edit/Update 1

UPDATE 1: why this code is working http://sqlfiddle.com/#!9/acf65/2 why it is giving error in phpMyadmin

Question: phpMyadmin version is 10.1.13-MariaDB but when i execute this (http://sqlfiddle.com/#!9/4a543/1) query of mysql 5.6 it will not work why?

thank you very much!!!

Answer 1

I see two syntax errors:

1. After when you open a parenthesis which is not a valid in a CASE statement. You close the parenthesis before AS, so you should delete it from there too.
2. a case statement ends with end and you don't have one. Put it before as

Example:

CASE option 
   WHEN condition THEN statement 
   [ELSE] statement 
END

Also, your first query seems a valid MySQL query. Why don't you use that one?

Answer 2

Instead of Case use IF condition, have look into your both query in error log you have 'tn' it means you are running wrong query, check this

SELECT product_id, NOW() AS current_date,
    bt.date_from AS starts_on,
    bt.date_end AS ends_on,
    IF(tn.end >= NOW(),DATEDIFF(bt.date_end, NOW()),0) AS days_remaining
    FROM bookings AS bt;