How can I combine dates in Oracle?-CodePudding

I have a column SCHEDULE_DATE and two columns: RESET_HOUR and RESET_MINUTE. I need to combine them and create a new column with dateteime format. For example:

SCHEDULE_DATE	RESET_HOUR	RESET_MINUTE
28.01.2021	1	30
12.02.2021	0	15

I need like this:

SCHEDULE_DATE	RESET_HOUR	RESET_MINUTE	SCHEDULE_DATETIME
28.01.2021	1	30	28.01.2021 01:30
12.02.2021	0	15	12.02.2021 00:15

thank you!

CodePudding user response：

Assuming the SCHEDULE_DATETIME already exist, you may use the following update:

UPDATE yourTable
SET SCHEDULE_DATETIME = CAST(SCHEDULE_DATE AS timestamp)  
                            RESET_HOUR / 24.0   RESET_MINUTE / 60 / 24;

If you instead just want to view this computed column, then use the same logic in a select:

SELECT *, CAST(SCHEDULE_DATE AS timestamp)  
          RESET_HOUR / 24.0   RESET_MINUTE / 60 / 24 AS SCHEDULE_DATETIME
FROM yourTable;

CodePudding user response：

You can use:

SELECT t.*,
       TRUNC(schedule_date)
         NUMTODSINTERVAL( reset_hour, 'HOUR')
         NUMTODSINTERVAL( reset_minute, 'MINUTE') AS schedule_datetime
FROM   table_name;

or:

SELECT t.*,
       TRUNC(schedule_date)
         NUMTODSINTERVAL( reset_hour * 60   reset_minute, 'MINUTE') AS schedule_datetime
FROM   table_name;

or:

SELECT t.*,
       TRUNC(schedule_date)
         (reset_hour * 60   reset_minute, 'MINUTE') * INTERVAL '1' MINUTE
         AS schedule_datetime
FROM   table_name;