date format in oracle custom

Question

I want to create a table Emp with the dob in the format ddmmyy but I'm not sure how to do it, this is what I've come up with so far

Emp(eno,name,dob);

create table Emp(
       eno number constraint c1 as primary key
       , name varchar2(50)
       , dob date
);

A date in Oracle does not have a format. The client decides how to format the date into a human-readable string. If the client application doesn't make that decision explicitly, the session's nls_date_format would be used. So the question doesn't make sense. You've also tagged this for MySQL which doesn't make sense given the rest of your question. — Justin Cave
– Justin Cave, Commented Apr 26, 2022 at 13:42
There is no "format" for the storage of a date. Formatting is a presentation issue, and to convert a DATE type to a string, you call to_char(dob,'DDMMYY') — OldProgrammer
– OldProgrammer, Commented Apr 26, 2022 at 13:43
exatcly , date format is just a presentation , in dob column you save datetime datatype — eshirvana
– eshirvana, Commented Apr 26, 2022 at 14:00
There is an internal format for storing dates, obviously, otherwise there would need to be an extra variable needed to decode an arbitrary internal format. What you seem to be doing is conflating data with presentation. The format for displaying data is in the purview of either the business logic layer (not so good) or with the presentation layer (better). Consider the alter session command to be a way for the business logic layer to contain some aspects of the presentation layer. — Jeff Holt
– Jeff Holt, Commented Apr 26, 2022 at 14:11

MT0 · Accepted Answer · 2022-04-26 14:32:07Z

In Oracle, a DATE is a binary data-type consisting of 7-bytes representing: century, year-of-century, month, day, hour, minute and second. It ALWAYS has those 7 components and it NEVER stores any particular (human-readable) format.

I want to create a table Emp with the dob in the format ddmmyy

You cannot as a DATE never stores any format.

What you can do is enforce that the time component will always be midnight and, if you wanted the formatting to be managed in the table, you could add a virtual column:

CREATE TABLE Emp(
  eno  NUMBER(10,0)
       GENERATED ALWAYS AS IDENTITY
       CONSTRAINT emp__eno__pk PRIMARY KEY,
  name VARCHAR2(50),
  dob  DATE
       CONSTRAINT emp__dob__chk CHECK (dob = TRUNC(dob)),
  formatted_dob VARCHAR2(6)
       GENERATED ALWAYS AS (TO_CHAR(dob, 'DDMMYY'))
);

Then you can:

INSERT INTO emp (name, dob) VALUES ('Alice', TRUNC(SYSDATE));

and the table contains:

ENO NAME DOB FORMATTED_DOB

1 Alice 2022-04-26T00:00:00 260422

Note: the dob column is formatted according to the the configuration of the client application you are using to access the database. In the example above, it is set to use an ISO8601 format.

However, it is more usual to treat the formatting of a DATE as a display issue and then you do not need a virtual column and can set whatever format you require in the SQL statement (or in the 3rd party application used to access the database). For example, in SQL you could use:

SELECT eno,
       name,
       TO_CHAR(dob, 'DDMMYY') AS dob
FROM   emp;

db<>fiddle here

Collectives™ on Stack Overflow

date format in oracle custom

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related