Inserting a value - Oracle sql date field format

0
votes

From what I have seen so far, when inserting into a table that has date value such as "27-10-2004", the following format DD-MON-YYYY is used

insert into tableName values('27-Oct-2004')

Is it possible to insert the date in its original format '27-10-2004' into the table? In general how many variations of this date may be considered correct for inserting into tables (this is without using any built-in functions, just SQL)?

1
sqloracledate

1 Answers

3
votes

Use a date literal in Oracle:

insert into tableName
    values (DATE '2004-10-27');

This defines a date constant using the ISO standard format YYYY-MM-DD.

If you wanted to use a specific format -- which I wouldn't recommend -- then you can convert to a date using to_date():

insert into tableName
    values (to_date('27-10-2004', 'DD-MM-YYYY'));

Any particular string that you use is interpreted based on system settings, so it can vary by language and geography.

Note: When inserting a value, you should include the column names. I assume that your code is just for illustrative purposes.