DBMS date command

Question

whenever I'm trying to insert this query I'm getting an error.

CREATE TABLE dateOfBirth(dateOfBirth date);
INSERT INTO dateOfBirth(dateOfBirth)VALUES('1967-11-17');

I'm getting this error:

ORA-01843: not a valid month

Answer 1

Let me try your code:

SQL> CREATE TABLE dateOfBirth(dateOfBirth date);

Table created.

SQL> INSERT INTO dateOfBirth(dateOfBirth)VALUES('1967-11-17');
INSERT INTO dateOfBirth(dateOfBirth)VALUES('1967-11-17')
                                           *
ERROR at line 1:
ORA-01861: literal does not match format string


SQL>

Doesn't work either, but - failed with a different error.

---

Basically, that's what happens (I mean - you get errors) when you're trying to enter a string into a date datatype column. Although it is clear at the first glance that - if you're inserting '1967-11-17' into dateOfBirth - that someone was born on 17th of November 1967. Oracle, unfortunately, isn't that enthusiastic about it. It will try to implicitly convert string to date, but - as you can see, in both your and my case it miserably failed.

Besides, what if you tried to enter e.g. 12-07-05. What is what in this string? Is 12 year (could be), month (could be) or day (could be as well)? The same goes for 07 and 05. Simply, don't do that, don't rely on implicit conversion from anything to something else.

So, what can you do about it? Obviously, insert a valid DATE datatype value! How? For example:

• use date literal which always looks like date 'yyyy-mm-dd':

  SQL> insert into dateofbirth (dateofbirth) values (date '1967-11-17');
  
  1 row created.
  

• or, use to_date function with appropriate format mask:

  SQL> insert into dateofbirth (dateofbirth) values (to_date('17.11.1967', 'dd.mm.yyyy'));
  
  1 row created.
  

• or, alter session and set date format so that Oracle recognizes it (as you'll see, your "initial" insert will now succeed):

  SQL> alter session set nls_date_format = 'yyyy-mm-dd';
  
  Session altered.
  
  SQL> INSERT INTO dateOfBirth(dateOfBirth)VALUES('1967-11-17');
  
  1 row created.
  

---

Quite a few options. Therefore, if you take control over it, there'll be no problem at all.

Answer 2

Your insert statement tries to squeeze a string into a date column. Oracle must use implicit conversion in order to allow this to happen, this will use the sessions NLS parameters. In your example, the implicit conversion does not work with the string you have given it.

Implicit conversion is an easy way to end up with corrupt data or errors (as you are seeing now), you should always use explicit conversion so that you can be sure the string is being converted with the correct date format no matter what the session's settings are:

CREATE TABLE dateOfBirth(dateOfBirth date);
INSERT INTO dateOfBirth(dateOfBirth)VALUES(to_date('1967-11-17','yyyy-mm-dd'));

Once the data is stored in the table as a date, it can be displayed with whatever date format you prefer:

select to_char(dateOfBirth,'dd/mm/yyyy') dateOfBirth from dateOfBirth;

Or you can leave it up to the client's session settings to decide how to display it (making it the responsibility of the client to make it correct).

select dateOfBirth from dateOfBirth;