CODEWITHSUNDEEP
sqloracle-databasedate
DaMonkeyMan123
DaMonkeyMan123

Reputation: 15

SQL - Round up a date/time to the coming Sunday

Looking to round up half hour interviews to the coming Sunday. My query has a column (titled Trading Interval) with date and time stamps such as 02/03/2020 08:30 and 29/02/2020 19:00.

I want to generate a new column titled Week Ending that rounds these dates/times up to the coming Sunday.

So for the date 29/02/2020 19:00 it would round up and return 01/03/2020 00:00 and for the date 02/03/2020 it would round up to 08/03/2020 00:00

Thanks

Upvotes: 1

Views: 191

Answers (1)

MT0
MT0

Reputation: 167962

Use TRUNC to trunacate the date back to midnight and then NEXT_DAY to get the next sunday:

SELECT trading_interval,
       NEXT_DAY( TRUNC( trading_interval ), 'SUNDAY' ) AS week_ending
FROM   your_table;

So, for the test data:

CREATE TABLE your_table ( trading_interval ) AS
SELECT DATE '2020-03-02' + INTERVAL '08:30' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT DATE '2020-02-29' + INTERVAL '19:00' HOUR TO MINUTE FROM DUAL;

This outputs:

TRADING_INTERVAL    | WEEK_ENDING        
:------------------ | :------------------
2020-03-02 08:30:00 | 2020-03-08 00:00:00
2020-02-29 19:00:00 | 2020-03-01 00:00:00

db<>fiddle here

Upvotes: 4

Related Questions