MySQL converting integer to a month

[Pachuca]

Hi,

I hope there are some mysql people on here

I could use your help.

I'm trying to convert a table that uses double like 2.5 or 445.56 into a date, a month to be exact. I tried using DATE_ADD and MONTH function, but can't seem to get the right results. For example I would use SELECT day FROM table WHERE DATE_ADD('2014-01-01', INTERVAL 31 DAY); but it just spits out the same field. What should I be doing? 

[LegionDD]

Erik's correct, ofc, however your question is incomplete.

If you're trying to convert a field of type double with arbitrary values into a date, you'll need to know what those values mean.

Take for example the venerable Unix Timestamp, which is a value of seconds elapsed since "Thursday 1st January 1970 12:00:00 AM UTC"

 

Presuming, from what is in your question, that what you have in the "day" field of your table is the days elapsed since 2014-01-01, and you wanted to get the month of the date that results from adding those days to the presumed origin date of 2014-01-01, you'd write something like this:

 

SELECT MONTH(DATE_ADD('2014-01-01', INTERVAL `day` DAY)) FROM `table`;

This will suffice to get an accurate month. Keep in mind however, that this operation only deals with integer values and as such the double value in "day" will be rounded to the nearest integer.

Now obviously, if you need the double precision in your calculations you'll have to do some maths to convert your double into appropriate integer values (keep the value before the decimal point as days, convert the value behind the decimal point to hours and add that with "INTERVAL <hours> HOUR" to the date calculated from the days).