MySQL – How to Concatenate Date and Varchar

castconcatdateMySQL

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?

Best Answer

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 ;
Related Question