Shrivatsan Shrivatsan - 16 days ago 5
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?

Answer

Actually i found something that will suit my requirement.

https://community.oracle.com/thread/2587161?start=0&tstart=0

   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