CODEWITHSUNDEEP
sqloracle-databaseoracle19c
Black Swan
Black Swan

Reputation: 853

How to save only Date in Oracle 19c without time

I am not used to with Oracle, same thing can be done in MySQL. My task is that i have to save date in Oracle 19c, but no time. My Application language is J2EE. After doing some search found some solutions but my requirement is not being filled yet.

Solution 1 : Insert a Datetime Value Using the TO_DATE Function

CREATE TABLE abe_student (
first_name VARCHAR2(50),
last_name VARCHAR2(50),
date_of_birth DATE
);

INSERT INTO abe_student (first_name, last_name, date_of_birth) 
VALUES ('John', 'Doe', TO_DATE('2016/01/16', 'yyyy/mm/dd'));

INSERT INTO abe_student (first_name, last_name, date_of_birth) 
VALUES ('John', 'Doe', TO_DATE('16/01/2016', 'dd/mm/yyyy'));

This solution was for 10g. I have tried this one but it is inserting time with all zeros. Both queries are giving the same result.

DB insert result

solution two (10g): trunc()

  1. remove all times from your DATE column (update yourtable set yourdatecolumn = trunc(yourdatecolumn))

  2. ensure that all future dates contain no time part by placing a check constraint on the column by using check (yourdatecolumn = trunc(yourdatecolumn))

  3. adjust all your INSERT and UPDATE statements or -if you're lucky- adjust your API, to only insert TRUNCed dates.

As per the instructions the query should be like :

update ABE_STUDENT set ABE_STUDENT.DATE_OF_BIRTH = trunc(DATE_OF_BIRTH);

After following the step 1, i have checked the DB, but no change.

TRUNC() is not working but TO_CHAR() is wokring fine.

SELECT TRUNC(DATE_OF_BIRTH), to_char(DATE_OF_BIRTH,'MM/DD/YYYY')  FROM ABE_STUDENT

result after truc and to_char

Lets introduce the use case why I need the data that format. The concept will be used for a financial service transactions analysis, where I need just the date. In complex query time, using another method to format the date, will be a performance issue.

update after using : TRUNC(DATE_OF_BIRTH, 'DAY')

SELECT TRUNC(DATE_OF_BIRTH, 'DAY'), to_char(DATE_OF_BIRTH,'MM/DD/YYYY')  FROM ABE_STUDENT

enter image description here

Upvotes: 0

Views: 1712

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270191

You can use a generated column to generate the string form. I would use:

alter table abe_student add column dob_string varchar2(255)
    generated always as (to_char(date_of_birth, 'YYYY-MM-DD'));

(I have a strong preference for standard date formats.)

Then you can query the table using the string column. Voila! It will appear exactly as you want it.

You can ensure that the column has no time component using a check constraint:

alter table abe_student add constraint chk_abe_student_dob
    check (date_of_birth = trunc(date_of_birth));

Or you could configure your GUI interface to display only the date for a date column.

Upvotes: 0

MT0
MT0

Reputation: 168147

You cannot.

A DATE in Oracle is a binary data-type which ALWAYS contains 7-bytes representing century, year-of-century, month, day, hour, minute and second.

If you want to display a DATE data type without the time component then convert it to a formatted string (either using the TO_CHAR function in the query or in the 3rd-party application you are using to access the database).

If you want to store the DATE without regards to the time component then insert the values so they always have the time component at midnight; you can use a CHECK constraint to enforce this.

Upvotes: 1

Related Questions