CODEWITHSUNDEEP
sqloracle-database
Harry Jones
Harry Jones

Reputation: 73

Inserting TIME into table SQL

I'm trying to insert a TIME value in a table in SQL Developer, but I get the current month returned. I am using Date as the constraint when creating the table. Sorry for the poorly written code, I'm not familiar with SQL.

INSERT INTO DELIVERY(ORDER_ID, DELIVERY_ID, DELIVERY_DATE, DELIVERY_TIME)
VALUES(1, 1565412,('06/Sep/12'),(TO_DATE('16:18:14', 'hh24:mi:ss')));

Output:

ORDER_ID DELIVERY_ID DELIVERY_DATE DELIVERY_TIME 

     1     1565412 06-SEP-12     01-FEB-15     
     2     8456233 24-MAR-12     01-FEB-15     
     3     8412654 21-JUN-12     01-FEB-15     
     4     1124335 03-JUN-11     01-FEB-15     
     5     7218854 30-AUG-11     01-FEB-15 `

Here is the table:

CREATE TABLE DELIVERY (
ORDER_ID      NUMBER(7),
DELIVERY_ID   NUMBER(7) CONSTRAINT DELIVERY_ID_NN   NOT NULL,
DELIVERY_DATE DATE      CONSTRAINT DELIVERY_DATE_NN NOT NULL, 
DELIVERY_TIME DATE      CONSTRAINT DELIVERY_TIME_NN NOT NULL,
PRIMARY KEY(DELIVERY_ID),
FOREIGN KEY (ORDER_ID)  REFERENCES ORDERS(ORDER_ID)
);

When I 'SELECT delivery_time FROM DELIVERY` I want to be shown the time I have inserted into the table, not the date. I don't understand why it shows me 01-FEB-15

Upvotes: 0

Views: 11288

Answers (1)

tilley31
tilley31

Reputation: 688

Your time is being stored ok. However, you need to put a mask on your DELIVERY_TIME column to display only the time. Try this query:

select order_id, delivery_id, delivery_date, to_char(delivery_time,'hh24:mi:ss') 
from delivery

You can also set your NLS settings in SQL Developer to display the time by default in the menu

  • Tools
  • Preferences
  • Database
  • NLS,

then set the field Date Format (this is in version 4.0.0.13, I don't know if it changes in other versions).

Upvotes: 1

Related Questions