CODEWITHSUNDEEP
sqloracle-databasedatetime
Madan Kumar
Madan Kumar

Reputation: 11

How to split a datetime column in to two columns of Date and time separately in Oracle SQL?

I have a datetime column.

I want two columns: a date and a time column.

How can I split my column into two?

Upvotes: 0

Views: 1851

Answers (3)

MT0
MT0

Reputation: 167982

Use:

  • a DATE data-type with the time component set to midnight for the date (you can enforce this with a check constraint); and
  • an INTERVAL DAY(0) TO SECOND data-type for the time component.
CREATE TABLE table_name(
  datetime_column DATE,
  date_column DATE,
  time_column INTERVAL DAY(0) TO SECOND,
  CONSTRAINT table_name__date_column__chk CHECK (date_column = TRUNC(date_column))
)

If you want to get the combined date-time then you can easily add the two to get back to a date-time value.

How can I split my column into two?

Assuming you have the columns you can use:

UPDATE table_name
SET date_column = TRUNC(datetime_column),
    time_column = (datetime_column - TRUNC(datetime_column)) DAY TO SECOND;

db<>fiddle here

Upvotes: 1

Shinto Joy
Shinto Joy

Reputation: 31

Since there is no specific datatype for time, here my suggestion would be to keep the datetime in main column and add two VIRTUAL COLUMN for date value and time value respectively.

Oracle 11g has introduced a new feature that allows you to create a VIRTUAL COLUMN, an empty column that contains a function upon other table columns (the function itself is stored in the data dictionary).

However, it all depends on what you are going to do with it. Please elaborate your requirement so that you will get a more specific answer.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142713

As Gordon commented, there's no time datatype in Oracle.

Though, literally answering what you asked, you can separate date and time and store each of them into their own columns - it's just that these will be VARCHAR2 columns and you can only look at how pretty they are. You can't, for example, do any date arithmetic on them; first you'd have to convert them back to date datatype, so question is what you really want to do with what you get.

Anyway, here you are:

SQL> create table test
  2    (datum      date,
  3     date_only  varchar2(10),
  4     time_only  varchar2(8)
  5    );

Table created.

Sample value:

SQL> insert into test (datum) values (sysdate);

1 row created.

Split date to two parts:

SQL> update test set
  2    date_only = to_char(datum, 'dd.mm.yyyy'),
  3    time_only = to_char(datum, 'hh24:mi:ss');

1 row updated.

What's in there?

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select * from test;

DATUM               DATE_ONLY  TIME_ONL
------------------- ---------- --------
05.08.2021 21:05:06 05.08.2021 21:05:06

SQL>

Upvotes: 0

Related Questions