sql - Oracle update the time in a date field where the time appears in a different column

Question

I have a table called b_hearings. I have two date columns: hearingdt and hearingtm. Hearingdt has the correct date and hearingtm has the correct time. I need to merge or update so they appear together.

hearingdt           | hearingtm
---------------------------------------
2019-02-13 00:00:00   1899-12-30 14:30:00
2014-06-10 00:00:00   1899-12-30 09:00:00
2017-08-01 00:00:00   1899-12-30 09:30:00
2014-08-04 00:00:00   1899-12-30 09:00:00

What is the best approach? Should I add the time from hearingtm and put it in hearingdt? Should I create a new column and add the data there. I'm not sure how to split dates. Here is the result I'm looking for:

hearingdt
-------------------
2019-02-13 14:30:00
2014-06-10 09:00:00
2017-08-01 09:30:00
2014-08-04 09:00:00

Many thanks in advance for your help!

标签： sqloracle

Answer 1

You should store the values in a single column. To convert, you can use:

select to_date( (to_char(hearingdt, 'YYYY-MM-DD') ||
                 ' ' ||
                 to_char(hearingtm, 'HH24:MI:SS')
                ), 'YYYY-MM-DD HH24:MI:SS'
              )

You can do this in an update. Or as a computed column.