Harry Jones Harry Jones - 10 days ago 5
SQL Question

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

Answer

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).