- Home /

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

Comment

**Answer** by MikeyBronowski ·

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

Copyright 2019 Redgate Software.
Privacy Policy

- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Site Issues (NOT FOR DATABASE QUESTIONS)
- Explore
- Topics
- Questions
- Users
- Badges