How to get datetime from both date and time fields

[getmeidea]

Hi all,

I have a table,
account_transactions(trans_rid int primary key COMMENT 'Transaction ID',
trans_date date COMMENT 'Date of transaction',
trans_time time COMMENT 'Time of transaction')

I need a query which returns me two fields "Transaction ID" and "Transaction Date" time.

The "Transaction Date" should be of type datetime(using both trans_date and trans_time).

[r035198x]

Hi all,

I have a table,
account_transactions(trans_rid int primary key COMMENT 'Transaction ID',
trans_date date COMMENT 'Date of transaction',
trans_time time COMMENT 'Time of transaction')

I need a query which returns me two fields "Transaction ID" and "Transaction Date" time.

The "Transaction Date" should be of type datetime(using both trans_date and trans_time).

Your table design was wrong then. You should have stored one field with the DateTime type as explained in the refmanual here.

[getmeidea]

Your table design was wrong then. You should have stored one field with the DateTime type as explained in the refmanual here.

Yes, I know this is not the right way of designing. But it was already made.
Here i need the solution with query.

[mwasif]

You can CONCAT_WS() both the fields to get date and time e.g.

SELECT trans_rid, CONCAT_WS(' ', trans_date, trans_time) AS Transaction_Date FROM account_transactions;