delete data only between may 6 and may 18, 2022-CodePudding

I have a table with CLIENT_CONTACT data, where there is call data from 2020 to today.

How can I delete data only between May 6 and May 18, 2022, did I write correctly?

DELETE FROM CLIENT_CONTACT
WHERE rep_date >= TO_DATE('06.05.2022', 'dd.mm.yyyy') AND rep_date <= TO_DATE('18.05.2022', 'dd.mm.yyyy')

CodePudding user response：

did I write correctly?

Maybe.... in Oracle, a DATE is a binary data type consisting of 7 bytes representing century, year-of-century, month, day, hour, minute and second and it ALWAYS has those components; however, many client applications used to access the database will default to not showing the time component and only show the date component (but the time component still exists even if it is hidden).

You can change those client applications to show the entire date/time. For SQL*Plus and SQL Developer, you can use:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

What you have written is the equivalent of:

DELETE FROM CLIENT_CONTACT
WHERE  rep_date >= TIMESTAMP '2022-05-06 00:00:00'
AND    rep_date <= TIMESTAMP '2022-05-18 00:00:00'

As you can see, if there are any rows with between 2022-05-18 00:00:01 and 2022-05-18 23:59:59 then they will not be deleted.

If your rep_date column only contains time components at midnight then your code will work.

If you do want to delete all the rows from any time on 2022-05-18 then you need to use:

DELETE FROM CLIENT_CONTACT
WHERE  rep_date >= TO_DATE('06.05.2022', 'dd.mm.yyyy')
AND    rep_date <  TO_DATE('19.05.2022', 'dd.mm.yyyy')

You could also write it using DATE literals:

DELETE FROM CLIENT_CONTACT
WHERE  rep_date >= DATE '2022-05-06'
AND    rep_date <  DATE '2022-05-19'

or TIMESTAMP literals:

DELETE FROM CLIENT_CONTACT
WHERE  rep_date >= TIMESTAMP '2022-05-06 00:00:00'
AND    rep_date <  TIMESTAMP '2022-05-19 00:00:00'

Or, if you want to pass in 18.05.2022 then you can add a day:

DELETE FROM CLIENT_CONTACT
WHERE  rep_date >= TO_DATE('06.05.2022', 'dd.mm.yyyy')
AND    rep_date <  TO_DATE('18.05.2022', 'dd.mm.yyyy')   INTERVAL '1' DAY

DELETE FROM CLIENT_CONTACT
WHERE  rep_date >= TO_DATE('06.05.2022', 'dd.mm.yyyy')
AND    rep_date <  TO_DATE('18.05.2022', 'dd.mm.yyyy')   1

You could also use your code but wrap rep_date in the TRUNC function to truncate the time component back to midnight:

DELETE FROM CLIENT_CONTACT
WHERE  TRUNC(rep_date) >= TO_DATE('06.05.2022', 'dd.mm.yyyy')
AND    TRUNC(rep_date) <= TO_DATE('18.05.2022', 'dd.mm.yyyy')

However, that would not be able to use an index on the rep_date column and you would need to use a function-based index on TRUNC(rep_date) so it is probably better not to use this method and to get into the habit of using date ranges directly on the column if you can.