Re: How do I insert records with date fields?

Ted Unnikumaran wrote
> when I try
> insert into lot_info values ('9/9/1999');
> it gives me an error that says 'not a valid month'

That is because Oracle uses a default date format when you do not give a date format yourself. The default format is the same you see when you do

    select sysdate
    from dual;

and most likely is 'DD-MON-YY'. This implies you could insert it using

    insert into lot_info (starting_date)     values ( '09-SEP-99' );

However, to avoid problems, you should ALWAYS specify the format yourself, and always use 4 digits for the year:

    insert into lot_info (starting_date)     values (to_date( '9/9/1999', 'dd/mm/yyyy'));

I am not sure which 9 is your month -- in the example above it is the second.

Note that when you use yyyy to insert 99, like

    insert into lot_info (starting_date)     values (to_date( '9/9/99', dd/mm/yyyy'));

    select starting_date
    , to_char( starting_date, 'dd-mon-yyyy hh24:mi:ss' )     from lot_info;

you will see 0099 for the year, not 1999... Check your docs for the RR and RRRR year formats and the difference between hh and hh24, and between mon, Mon and MON. Also note that Oracle always stores the time as well, although it may be set to 00:00:00 midnight to effectively only store the date:

    select to_char(sysdate, 'dd-mon-yyyy hh24:mi:ss')     from dual;

    insert into lot_info (starting_date)     values (sysdate); /* store time as well */

    insert into lot_info (starting_date)     values (trunc(sysdate)); /* set time to 0:00:00 */

    select starting_date
    , to_char( starting_date, 'dd-mon-yyyy hh24:mi:ss' )     from lot_info;

Dates are very important. Read your docs!

Arjan. Received on Sun Apr 25 1999 - 04:12:40 CDT