creating table in Oracle with Date

Question

I want to create a table in Oracle 10g and I want to specify the date format for my date column. If I use the below syntax: ``` create table datetest( ........ startdate date); ``` Then the date column will accept the date format `DD-MON-YY` which I dont want. I want the syntax for my date column to be `MM-DD-YYYY` Please let me know how to proceed with this. Regards,

Accepted Answer

A `DATE` has no inherent format. It is not simply a string that happens to represent a date. Oracle has its own internal format for storing date values.

Formats come into play when actual date values need to be converted into strings or vice versa, which of course happens a lot since interactively we write dates out as strings.

The default date format for your database is determined by the settings `NLS_DATE_FORMAT`, which you probably have set to `DD-MON-YYYY` (which I believe is the default setting for American English locales). You can change this at the database level or for a single session for convenience, but in general it is safer programming practice to be explicit so that you don't get errors or, worse, wrong results if your code is run in a different environment.

The simplest way to specify a date value unambiguously is a date literal, which is the word 'date' followed by a string representing the date in YYYY-MM-DD format, e.g. `date '2012-11-13'`. The Oracle parser directly translates this into the corresponding internal date value.

If you want to use a different format, then I recommend explicitly using TO_CHAR/TO_DATE with your desired format model in your code. Examples:

```
INSERT INTO my_table (my_date) VALUES ( TO_DATE( '11-13-2012', 'MM-DD-YYYY' ) );

SELECT TO_CHAR( my_date, 'MM-DD-YYYY' ) FROM my_table;
```

creating table in Oracle with Date

Solution

Problem