How to add date and time in Oracle

Question

I have the following data:

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 to return date_time , I only get 08-Dec-14 , not time. I set this field as DATE.

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

Thanks in advance.

Answer 1

use a select query like below

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

The format of the required date is: "DD-MON-YY HH24: MI: SS" or "DD / MM / YYYY" etc.

Answer 2

When I query the database to return date_time, I only get 08-Dec-14, not time. I set this field as DATE.

This is the display format that your client uses. it depends on the local NLS date settings.

You can override local NLS parameters with TO_CHAR at the individual request level.

For instance,

 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