question

yussefsaeed avatar image
yussefsaeed asked

I want to convert days to years, months and remaing days in oracle sql I have the number of days for example: 398 days, how to convert 398 days to number of years and number of months and remaining days like 398 days 1 year , 1 month and 2 days.

I want to convert days to years, months and remaing days in oracle sql

oracle-sql-developer
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
MikeyBronowski avatar image
MikeyBronowski answered

The proposed solution assumes:

  • the year is 365 days (not counting the leap days, as we do not have a date range)
  • the month is 31 days


I am using two functions :

  • integer division / - to catch full years, months - removing fractions
  • modulus division % - to catch reminders

I would do it this way in SQL Server:

years = integer number without fractions, i.e. 398 / 365 = 1 and 33/365, so it will be 1 year

months = reminder after taking all the integer years, i.e. 398 % 365 = 33, so it will be 33 days

Now, need to repeat the operation with integer division to get full months: 33 / 31 = 1 and 2/33, so 1 month

days = finally, taking reminder after all operations which is 398 % 365 % 31 = 2, so 2 days

DECLARE @daysBig INT = 398;
SELECT @daysBig AS [daysBig], @daysBig/365 [years], @daysBig%365/31 [month], @daysBig%365%31 AS [days];


The result would be

daysBig    years    month    days
398    1    1    2


Just bear in mind this is a very simplified solution and may produce some values that are not reflected in the real calendar. As example run this query and see values around the end of the year (365-366).

WITH Ints AS
(
  SELECT i = ROW_NUMBER() OVER (ORDER BY [object_id]) 
  FROM sys.all_objects 

)
SELECT i AS [daysBig], i/365 [years], i%365/31 [month], i%365%31 AS [days] FROM Ints
ORDER BY i;


Because we are assuming every month is 31 days the results will be like this

daysBig    years    month    days
364    0    11    23
365    1    0    0



10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.