How to get datetime from both date and time fields

[getmeidea]

Hi all,

I have a table,
account_transac tions(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 "Transactio n ID" and "Transactio n Date" time.

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

[r035198x]

Hi all,

I have a table,
account_transac tions(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 "Transactio n ID" and "Transactio n Date" time.

The "Transactio n 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.

[CODE=mysql]SELECT trans_rid, CONCAT_WS(' ', trans_date, trans_time) AS Transaction_Dat e FROM account_transac tions;[/CODE]