Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How can I insert date which is before 1950?

update up_sif.tb_person set dateB =to_date('18.10.1949','DD.MM.YYYY') where ID = '123345';

It keeps update with date 18.19.2049.

Any ideas?

share|improve this question
If that is the statement you are running, the year will be 1949 (I'm assuming that the 19 in 18.19 is a typo). What makes you believe that the year is 2049? Are you sure that your actual UPDATE statement isn't using the RR format mask? Or that you're using the RR format mask to display the data? – Justin Cave Oct 9 '13 at 7:25
Yes 19 is a typo. It updates with 18.10.2049. I also tried to set NLS_DATE_FORMAT (ALTER SESSION set NLS_DATE_FORMAT = 'DD.MM.YYYY';) but there's no effect. – DiaMonD Oct 9 '13 at 7:43
What makes you believe that the year is 2049? Post a complete, reproducible test case that shows us exactly what you're seeing. – Justin Cave Oct 9 '13 at 7:49
What does SELECT TO_CHAR(dateB, 'DD-MM-YYYY') FROM up_sif.tb_person WHERE id = '123345' ; show? – ypercubeᵀᴹ Oct 9 '13 at 7:50
You should also learn about date constants. These are specified as date 'YYY-MM-DD', so your update statement can be shortened/simplified to update up_sif.tb_person set dateB = date '1949-10-18' where ID = '123345';. In addition, date constants use the ISO standard date format, which is a good practice to be following. – Colin 't Hart Oct 9 '13 at 10:19

Try this:

create table date_ ( date_ date );

insert into date_ values(to_date('20130331','YYYYMMDD'))


update date_ set date_=to_date('19491018','YYYYMMDD') commit;

share|improve this answer
Learn about date constants, please. – Colin 't Hart Oct 9 '13 at 10:19

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.