Data provided to import into Oracle only has day and month. Year is missing. I need to run some queries to calculate number of days between dates. Eg. Checkin date: 03-April and Check out: 07-May. What DATE type to use?

CodePudding user response：

I need to run some queries to calculate number of days between dates. Eg. Check in date: 03-April and Check out: 07-May.

In general, you need a year to unambiguously calculate the number of days between two dates as you need to account for leap years (and, if you are being particularly precise, leap seconds).

You can use several methods:

1. Assume that your dates are always going to be in a non-leap year. Then you can pick any non-leap year and use it in your query as a default year.
2. Assume that your dates are always going to be in a leap year. Then you can pick any leap year and use it in your query as a default year.
3. Assume that your dates are always going to be of the current year.

They all have flaws:

• If the assumption is wrong then you will get the wrong value if the dates are either side of Feb-29.
• If you get the value Feb-29 then 1 & 3 can raise exceptions.

What DATE type to use?

If you are talking about how to store the value in a table then either store the value in a VARCHAR2 if you want to store the raw values or chose one of the three assumptions above and use a DATE data type and apply the default year.

For example, you could store the raw data and calculate the DATE values for the default year:

CREATE TABLE table_name (
  check_in            VARCHAR2(20),
  check_out           VARCHAR2(20),
  check_in_1970       DATE GENERATED ALWAYS AS (TO_DATE(check_in || 1970 DEFAULT NULL ON CONVERSION ERROR, 'DD-MonthYYYY')),
  check_out_1970      DATE GENERATED ALWAYS AS (TO_DATE(check_in || 1970 DEFAULT NULL ON CONVERSION ERROR, 'DD-MonthYYYY')),
  check_in_2020       DATE GENERATED ALWAYS AS (TO_DATE(check_in || 2020 DEFAULT NULL ON CONVERSION ERROR, 'DD-MonthYYYY')),
  check_out_2020      DATE GENERATED ALWAYS AS (TO_DATE(check_in || 2020 DEFAULT NULL ON CONVERSION ERROR, 'DD-MonthYYYY')),
);

INSERT INTO table_name (check_in, check_out) VALUES ('03-April', '07-May');
INSERT INTO table_name (check_in, check_out) VALUES ('01-January', '31-December');
INSERT INTO table_name (check_in, check_out) VALUES ('29-February', '29-February');

Then:

SELECT check_in,
       check_out,
       check_out_1970 - check_in_1970 AS diff_1970,
       check_out_2020 - check_in_2020 AS diff_2020
FROM   table_name;

Outputs:

CHECK_IN	CHECK_OUT	DIFF_1970	DIFF_2020
03-April	07-May	34	34
01-January	31-December	364	365
29-February	29-February	null	0

db<>fiddle here