Query from SQL to MySQL

Question

Can anyone help me convert this SQL statement into MySQL, because i'm getting syntax error when i tried to run it from MySQL.

SELECT
    a.userid,
    CAST(a.checktime AS VARCHAR) AS timein,
    ISNULL(CAST(b.checktime AS VARCHAR),'NO TIMEOUT') AS timeout
FROM    timein a
LEFT JOIN timeout b ON a.userid = b.userid
WHERE   ISNULL(CAST(b.checktime),'NO TIMEOUT') = 'NO TIMEOUT'

Replace ISNULL By IFNULL

1000111
– 1000111

2016-05-30 06:38:16 +00:00
Commented May 30, 2016 at 6:38 — 1000111
– 1000111, Commented May 30, 2016 at 6:38
Add the error message please

Jens
– Jens

2016-05-30 06:39:27 +00:00
Commented May 30, 2016 at 6:39 — Jens
– Jens, Commented May 30, 2016 at 6:39

sagi · Accepted Answer · 2016-05-30 07:04:07Z

1

Use COALESCE() instead of ISNULL() which is SQL-Server syntax.

SELECT
    a.userid,
    a.checktime AS timein,
    COALESCE(b.checktime,'NO TIMEOUT') AS timeout
FROM    timein a
LEFT JOIN timeout b ON a.userid = b.userid
WHERE   COALESCE(b.checktime,'NO TIMEOUT') = 'NO TIMEOUT'

edited May 30, 2016 at 7:04

answered May 30, 2016 at 6:42

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Seryu Over a year ago

thanks, but still getting syntax error near here CAST(a.checktime as varchar) AS timein, COALESCE( CAST(b.checktime as varchar),'NO TIMEOUT') AS timeout

sagi Over a year ago

What type is checktime column ?

Seryu Over a year ago

i declared it as Varchar

sagi Over a year ago

@Seryu Then why the hell do you cast it to varchar? Try the query now.

1000111 · Accepted Answer · 2016-05-30 07:08:58Z

1

SELECT
    a.userid,
    CAST(a.checktime AS CHAR(100)) AS timein,
    IFNULL(CAST(b.checktime AS CHAR(100)),'NO TIMEOUT') AS timeout
FROM    timein a
LEFT JOIN timeout b ON a.userid = b.userid
WHERE   IFNULL(CAST(b.checktime),'NO TIMEOUT') = 'NO TIMEOUT';

You need to cast as char datatype. (you need to put the correponding length of the varchar column in CHAR(LENGTH) )
ISNULL function in MySQL takes one parameter whereas in SQL SERVER it takes two. You should use IFNULL or COALESCE (as sagi mentioned).

Note about CAST :

The CAST() function converts a value of any type into a value that has a specified type. The target type can be any one of the following types: BINARY, CHAR, DATE, DATETIME, TIME,DECIMAL, SIGNED, UNSIGNED .

edited May 30, 2016 at 7:08

answered May 30, 2016 at 7:03

1000111

13.5k2 gold badges32 silver badges43 bronze badges

Collectives™ on Stack Overflow

Query from SQL to MySQL

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related