Skip to content
Advertisement

MySQL convert timestamp and time string to DateTime format

In my table have two columns, one as timestamp to save the date and one as time string to save the time with period.
Eg:
enter image description here

I want to combine them into one column as DateTime format in the result then order by desc on that column.

Here is the example: http://sqlfiddle.com/#!9/25eb21/4
The column name ‘Datetime’ expected is Datetime or timestamps type, so I can sort correctly on it.

Advertisement

Answer

You do not need to convert the values to integers to add them. MySQL has built-in functions for this purpose:

SELECT *,
       addtime(apptDate, str_to_date(apptTime, '%%h:%%i %%p')) as datetime
FROM appt
ORDER BY Datetime DESC;

If apptTime is just a time value (which it should be), then you obviously do not need to convert from a string. I would usuggest fixing the data model.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement