Mysql

連接日期和 varchar

  • March 9, 2015

我需要從mysql的表中選擇儲存在列數據類型中的日期和儲存在列數據類型transactionDate中的時間。我的查詢就像date``transactionTime``varchar``transactionTable

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

兩列中都有值,但上面返回一個空值。如何連接datevarchar列?

當參數之一為空時,該CONCAT()函式返回。NULL您可以使用COALESCE()繞過問題,將空值轉換為空字元串 - 或任何其他常量值,例如'00:00:00'時間部分:

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

事實上,你並不真的需要一個顯式cast()的,強制轉換char為隱式的:

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

引用自:https://dba.stackexchange.com/questions/94745