1

I am need to select the date stored in a column transactionDate datatype date and time stored in column transactionTime datatype varchar from a table transactionTable in mysql. My query is like

select concat( CAST( transactionDate as char), transactionTime) as transaction  
from transactionTable 
where id = 123

There are values in both the columns but the above returns me a null. How can I concat date and varchar columns?

  • If both values (in both columns) are not null, you'll get a result. If one of the two columns is null, the result of the concat() will be null. What do you want instead? – ypercubeᵀᴹ Mar 9 '15 at 11:35
  • I would want it to return value of whichever isn't null in case one of them is null. – user3756152 Mar 9 '15 at 12:00
5

The CONCAT() function returns NULL when one of the arguments is null. You could use COALESCE() to bypass the issue, converting the nulls to empty strings - or to any other constant value, for example '00:00:00' for the time part:

select concat( coalesce(cast(transactionDate as char), ''), 
               ' ', 
               coalesce(transactionTime, '00:00:00')
             ) as transaction  
from transactionTable 
where id = 123 ;

In fact, you don't really need an explicit cast(), the cast to char is implicit:

select concat( coalesce(transactionDate, ''), 
               ' ', 
               coalesce(transactionTime, '00:00:00')
             ) as transaction  
from transactionTable 
where id = 123 ;
  • In this query, you better have to use ISNULL function instead of COALESCE. It will be faster and will give the same result. – Nico Mar 9 '15 at 13:08
  • 5
    @Nico what are you talking about? Can you prove your claim that ISNULL() is faster than COALESCE()? – ypercubeᵀᴹ Mar 9 '15 at 13:48
  • 4
    Not to mention that MySQL's ISNULL() returns either 1 or 0. It is different than SQL-Server's ISNULL() (which I guess you confused it with). – ypercubeᵀᴹ Mar 9 '15 at 13:57
  • Perhaps @Nico intended function IFNULL(). But the executions time is pretty much the same. – Giovanni Mar 9 '15 at 14:43
  • 1
    @ypercube You're right! I was talking about IFNULL() not ISNULL(). – Nico Mar 9 '15 at 15:15
1

You can also use CONCAT_WS function which (i) converts datatype automatically (ii) skips NULL values*:

SELECT CONCAT_WS(' ', transactionDate, transactionTime) AS whatever

* If transactionDate is NULL then the result won't contain leading space. If transactionTime is NULL then result won't contain trailing space.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.