Archive for March 9th, 2012

The mySQl FROM_DAYS Function

I find it interesting how seldom-used functions are often poorly documented. In the course of my research on SQL I have run across several of these types of functions. In this case we have the FROM_DAYS() function. This is what I wrote for the desk reference:

The FROM_DAYS() function can only be used for dates after the year 1582 (in most of Europe, but not England or Russia) because it was this year the Gregorian Calendar came into effect. The date adjustment made on that date is not taken into account in this function. This is meant to take the number of days since the date represented by 0000-00-00, or the first day anno domini. What is strange about it is that input less than 365 returns simply “0000-00-00”, but numbers of days after that will act properly. Example:

SELECT FROM_DAYS(367)

will result in the date “0001-00-02”.

Not sure why the function does not work for numbers less than 365. I tested the function for negative numbers as well, and there was no go there either. I suppose the creator of this function assumed that people would only be using it for dates after the Gregorian calendar was put into place. He is probably correct in this regard. Still, I was surprised to see it. Also, many examples on the web, especially the one on the oracle site for mySQL actually have incorrect output in their example when compared with the output I found in testing. Their results were exactly seven years off. Probably a typo.

No Comments