Create table with date column that has only specific columns

December 4, 2021

I have to create this table :

Temps_ED (DateFacture, month, year)

where :

DateFacture :day+month+year
month: (month+year)
year : year

>Solution :

Oracle has two data types for storing date-time values.

DATE
TIMESTAMP

The both always have the components year, month, day, hour, minute, second. TIMESTAMP can, optionally, have fractional seconds and/or a timestamp. Therefore, you must have a time component if you are using a DATE or TIMESTAMP data type.

what i’m asking for is if there’s a way to make a column type date but only in a specific format as in "month date ‘mm/dd’"

No, both DATE and TIMESTAMP are binary data types; they do not store any format. If you want a format then you need to convert it from binary data to formatted data (i.e. a string) but this does not mean that you should store your values as strings; you should store them as the binary data and then format it when you retrieve/display it.

One solution is to store a DATE and then use virtual columns to get the other formats; this will ensure that all the columns are consistent.

CREATE TABLE temps_ed (
  value        DATE,
  datefracture VARCHAR2(10)
               GENERATED ALWAYS AS (TO_CHAR(value, 'YYYY-MM-DD')),
  month        VARCHAR2(7)
               GENERATED ALWAYS AS (TO_CHAR(value, 'YYYY-MM')),
  year         NUMBER(4,0)
               GENERATED ALWAYS AS (EXTRACT(YEAR FROM value))
);

INSERT INTO temps_ed (value) VALUES (SYSDATE);

Then: