CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
user3786519
user3786519

Reputation: 21

What format does Oracle expect for a `DATE` value?

I used the relational model in SQL Model developer from Oracle to create this table:

  CREATE TABLE Orders
  (
    OrderNum                   INTEGER NOT NULL ,
    OrderDate                  DATE ,
    TIME                       DATE ,
    Employee_EMP_CODE          VARCHAR2 (10) NOT NULL ,
    Tax_Id                     VARCHAR2 (10) ,
    Tax_Rate                   VARCHAR2 (10) ,
    Customers_Customer_No      INTEGER NOT NULL ,
    Consoles_Console_NO        VARCHAR2 (10) NOT NULL ,
    Customers_Buyer_First_Name VARCHAR2 (10) NOT NULL ,
    Customers_Buyer_Last_Name  VARCHAR2 (10) NOT NULL ,
    Comments                   VARCHAR2 (250)
  ) ;

For some reason it will not take the time value when I try to insert a row. I have tried these formats

It keeps giving me the error that the month value isn't valid.

Upvotes: 0

Views: 173

Answers (3)

Dan Bracuk
Dan Bracuk

Reputation: 20804

Oracle is very annoying when it comes to date stuff. However, I have had good luck with this format:

{ts 'yyyy-mm-dd HH:mi:ss'}

year, month, day, hours, minutes, seconds. Use leading zeros if appropriate. Use 24 hour clock for hours.

Upvotes: 0

Panayiotis Savva
Panayiotis Savva

Reputation: 131

To answer your question "What format does Oracle expect for a DATE value?"

You can look at your NLS_DATE_FORMAT:

SQL> SHOW NLS_DATE_FORMAT

Assuming you're using Default, it should come back with something like: DD-MON-RR

You could actually insert or select without To_Date like this: Select * from Orders Where OrderDate = '25-DEC-04';

To answer your real question: How should I store my orderDate :)

It seems you're storing Order Date, and Order Time in two separate fields. There is really no need to do this, as this means you'll be using twice the disc space for your data field, which you only actually need 1 field.

as DATE datatype will store both Date and Time, you can actually just use 1 field, and this will make life a lot easier when trying to select your data as well.

You really should look at using sysdate at the moment your order is place. ie: 

SELECT TO_CHAR
    (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "OrderDate"
     FROM DUAL;

NOW
-------------------
04-13-2001 09:45:51

Also please notice that i've made use of TO_CHAR for printing purposes only. when inserting, you will simply do:

Insert into Orders(OrderDate, other fields) Values (sysdate, other fields);

I hope this helps.

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231661

An Oracle date column always has a day and a time component. It makes no sense to have two columns OrderDate and Time both of type date. Your OrderDate would have a time component and your Time would have a day component. Realistically, you just want a single column of type date that has both the day and the time that the order was placed.

If you are trying to insert data into a date column, you would either want to pass the proper data type from your client application (i.e. bind a date in your client application when you create your insert statement) or you would want to do an explicit conversion from a string using the to_date function. For example

INSERT INTO Orders( OrderNum, OrderDate, <<more columns>> )
  VALUES( 1, to_date( '2014-07-12 12:31:00', 'YYYY-MM-DD HH24:MI:SS' ), <<more values>> );

Upvotes: 2

Related Questions