Shrivatsan Shrivatsan - 3 months ago 28
SQL Question

How to convert and display an Integer into YYYYMMDD format using Select query without neglecting days and leap year logic?

I have a query to retrieve a set of non null records from a column x consisting of DATE format.

If count(x) = 35 then i need to display the value as 1 Month & 5 days

If 369 days then 1 year & 4 days or If 400 days then 1 year 1 month 5 days respectively

Query: In the above instance,unfortunately i am neglecting 0.25 days but How to tweak my actual requirement in such a way that i don't end up neglecting days and handle leap year logic too

How to solve this issue?


Actually i found something that will suit my requirement.

   select days,
   floor(days / 365.25) years,
   floor(mod(days,365.25) / (365.25 / 12)) months,
   round(mod(days,365.25 / 12)) days
   from periods

So this can produce expected output when number is given. This produces output as years,months and remaining days