How to add date and time in Oracle

Question

I have the following details:

date_time: 08-Dec-14 07:52:52 along with other fields.

I insert using the following:

insert into employee (DATE_TIME, NAME, EMPLOYEE_ID)
 values(TO_DATE('08-Dec-14 07:52:52','DD-MON-YY HH24:MI:SS'), 'XYZ', 123);

When I query the database for a return of thedate_time, I get only 08-Dec-14 and not the time. I have set this field as DATE.

What changes need to be made so I can get the time as well?

Thanks in advance.

Answer 1

use select query like below

select to_char(date_time,'Required date format') from employee.

Required date format: 'DD-MON-YY HH24:MI:SS' or'DD/MM/YYYY' etc.

Answer 2

When I query the database for a return of the date_time, I get only 08-Dec-14 and not the time. I have set this field as DATE.

It is a display format which your client is using. it depends on the locale-specific NLS date settings.

You can override the local NLS settings using TO_CHAR at individual query level.

For example,

SQL> alter session set nls_date_format='DD-MON-YYYY';

Session altered.

SQL> SELECT sysdate FROM dual;

SYSDATE
-----------
24-JUL-2015

Using TO_CHAR to override the NLS dependent format:

SQL> SELECT to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS') dt FROM dual;

DT
-------------------
07/24/2015 12:21:01