I have a sales table with created datetime, my business hours are from 9 AM to 2 AM in the night on the following day. I am trying to convert the dates into my business date.

01/08/22 09:39:12.000000000 AM  04:00 

Lets say I have a sale at 1 AM, this sale has to be considered in the previous day.

Any function that can help me solve this issue would be appreciated

CodePudding user response：

It might be a bit of an overkill, but you could just use EXTRACT:

WITH dat AS
(
  SELECT to_date('01/08/22 09:39:12','DD/MM/YY HH24:MI:SS') AS t_stmp FROM dual UNION ALL
  SELECT to_date('02/08/22 01:03:15','DD/MM/YY HH24:MI:SS') FROM dual UNION ALL
  SELECT to_date('02/08/22 08:27:33','DD/MM/YY HH24:MI:SS') FROM dual UNION ALL
  SELECT to_date('02/08/22 14:11:51','DD/MM/YY HH24:MI:SS') FROM dual UNION ALL
  SELECT to_date('02/08/22 02:01:15','DD/MM/YY HH24:MI:SS') FROM dual 
)
SELECT CASE WHEN EXTRACT(HOUR FROM CAST(t_stmp AS TIMESTAMP)) BETWEEN 2 AND 8 THEN  -1
            ELSE 0
       END   TRUNC(t_stmp,'DD') AS business_date
  FROM dat;

CodePudding user response：

It looks like you just need to make a 2 hour shift to get your sales in the right date. You can add or substract hours from DATE/DATETIME/TIMESTAMP data type. If your column is TIMESTAMP then it would be like this:

-- when selecting data for date of sales
SELECT TRUNC(your_column_name - INTERVAL '2' HOUR, 'dd') "SALE_DATE"
-- And/Or
WHERE TRUNC(your_column_name - INTERVAL '2' HOUR, 'dd') = :DATE_OF_SALES
-- TRUNC function always returns DATE datatype
--
-- The opposite conversion would be
CAST(your_datetime_column   INTERVAL '2' HOUR as TIMESTAMP)  ...

Here is the small sample with result:

SELECT 
    to_char(SYSDATE, 'dd.mm.yyyy hh24:mi:ss') "DATETIME",
    to_char(SYSDATE  - INTERVAL '2' HOUR, 'dd.mm.yyyy hh24:mi:ss') "DATETIME_MINUS_2H",
    to_char(SYSDATE    INTERVAL '2' HOUR, 'dd.mm.yyyy hh24:mi:ss')   "DATETIME_PLUS_2H",
    to_char(SYSDATE  - INTERVAL '10' HOUR, 'dd.mm.yyyy hh24:mi:ss')   "DATETIME_MINUS_10H"
FROM 
    DUAL
--  
--  R e s u l t
--  
--  DATETIME            DATETIME_MINUS_2H   DATETIME_PLUS_2H    DATETIME_MINUS_10H
--  ------------------- ------------------- ------------------- -------------------
--  07.08.2022 09:58:38 07.08.2022 07:58:38 07.08.2022 11:58:38 06.08.2022 23:58:38

The last column now has the date from day before.