29th of February and interval

Hi,

it has been a long time since I post something. I just relocated to a different country, the Netherlands, and I was dealing with so much paperwork and stuff. So far so good. Dutch people are so kind and warm, they help about everything. My first impression is amazing here.

Actually, I wasn’t planning to write about Oracle so soon but it is 29th of February, the magical day, and I got over 250 emails from my client databases in 7 hours. They are all saying ORA-01839: date not valid for month specified

Why? because of this piece of code of mine:

Check the create date and in case it is null use 10 years ago. what is wrong with that? “interval” doesn’t check all the date conditions. when you say subtract 10 year from today, it tries to go directly 10 years ago for the same day! As you know 29th of Feb is coming every 4 years and there were no 29th of Feb at 10 years ago. I forgot about this limitation of “interval”.

So what did I do? I can do many things but to put out the fire, I changed 10 years to 4 years. at least emails are stopped 🙂 I should come up with something stronger here.

By the way, create_date column is a “timestamp with time zone”  not a date.

thanks for reading.