How can I truncate a timestamp to make readjustments to a date in Oracle?-CodePudding

I have the following query in my SELECT from Oracle:

CASE
            WHEN to_char(ola.promise_date, 'D') = 2 THEN (ola.promise_date - 4)
            WHEN to_char(ola.promise_date, 'D') = 3 THEN (ola.promise_date - 4)
            WHEN to_char(ola.promise_date, 'D') > 3 AND to_char(ola.promise_date, 'D') < 7 THEN (ola.promise_date - 2)
            WHEN to_char(ola.promise_date, 'D') = 7 THEN (ola.promise_date - 2)
            WHEN to_char(ola.promise_date, 'D') = 1 THEN (ola.promise_date - 2)
END mod_Promise_date

"ola.promise_date" is a column of type TIMESTAMP, and I use the CASE statement in order to adjust the date, considering the day of the week of the original promise date (because of internal considerations of shipment, among other things) So, for example, something that has a promise date of 2021-11-07 00:00:00.0000000 will have a modified promise date of 2021-11-05 00:00:00.0000000 (considering Sunday as the seventh day)

Now, I run this query with a program made with C#, there I have a date picker in order to select specific promise dates, so the user can choose Sunday, November 7, 2021 and it will run the query as said before, this works good with the exception of promise dates that are something like 2021-11-07 23:59:00.0000000 because the query will consider the date as 8th of November instead of 7th, and this is not expected behavior, because the hour, minutes, seconds and fractional seconds are not really needed in this specific instance of the project.

Is there a way to ignore the hour, minutes, seconds and fractional seconds in the CASE-WHEN statement? Or floor down the date to consider everything in the same day as the day specified, without consideration of the time.

I'm pretty new using Oracle, so sorry if I'm not clear enough or if the query doesn't look viable.

CodePudding user response：

You can use TRUNC(datetime_value) to truncate a DATE or a TIMESTAMP data type and set the time component to midnight:

CASE TRUNC(ola.promise_date) - TRUNC(ola.promise_date, 'IW')
WHEN 0 /* Monday    */ THEN TRUNC(ola.promise_date) - 2
WHEN 1 /* Tuesday   */ THEN TRUNC(ola.promise_date) - 4
WHEN 2 /* Wednesday */ THEN TRUNC(ola.promise_date) - 4
WHEN 6 /* Sunday    */ THEN TRUNC(ola.promise_date) - 2
                       ELSE TRUNC(ola.promise_date) - 2
END mod_Promise_date

Note: TO_CHAR(datevalue, 'D') will give different values depending on where you are in the world as different territories consider the week to start on different days-of-the-week. If you want a territory-agnostic method of determining the day-of-the-week then you can find the difference from the start of the ISO week using TRUNC(datetime_value) - TRUNC(datetime_value, 'IW').

CodePudding user response：

If I understood you correctly, you'll just have to truncate that value and "remove" time component (i.e. reset it to midnight).

For example:

(just setting format; you don't have to do that)

SQL> alter session set nls_timestamp_format = 'dd.mm.yyyy hh24:mi:ss.ff6';

Session altered.

Query that shows the result:

SQL> select systimestamp,
  2         trunc(systimestamp) result
  3  from dual;

SYSTIMESTAMP                        RESULT
----------------------------------- -------------------
05.11.21 19:29:41,207000  01:00     05.11.2021 00:00:00

SQL>

You'd have

... THEN (trunc(ola.promise_date) - 4)