Derive Years & Months based upon days

Given that months--and years, with leap years--have varying numbers of days, how do you propose changing a number of days into a count of years, months, and days?

Considered 365 days as 1 year and 30 days as 1 month.

OK, but then you get an absurd result such as 729 days translating to:

1 year, 12 months, 4 days

I think you need to think about this some more :)

Are you using PHP or another language to format the output or do you need the result strictly from a SQL query?

Although I stand by my opinion that this is not a good way to approach the problem, here is how you would implement using the definition in http:#a38293176 ...

SELECT Id,
    CustomerName,
    BillDays, 
    BillDays / 365 AS Years,
    (BillDays % 365) / 30 AS Months,
    (BillDays % 365) % 30 AS Days
FROM SomeTable

Select allOpen in new window

Assumes BillDays is of type int, bigint, or smallint.

My Client requires the same definition as specified to you for years and months. So i just cannot change that, what you said above is exactly correct, but at last we all know that our clients wish is our command. Anyways thanks a many for the help.

DustinKikuchi,

If the data type is one of the "int family" of data types, there is no need for the FLOOR function.  When you divide an int by an int in SQL Server, the result is always truncated as if the FLOOR function were applied.

Patrick

One more thing if months are coming as 12 can they be taken as 1 year. As in your example where in 729 days translates to:

1 year, 12 months, 4 days:

can the above be translated to 2 year, 0 months , 4 days

But if we do that, consider these two cases: 730 and 729.

729   --->   2 years, 0 months, 4 days
730   --->   2 years, 0 months, 0 days

Consider also:

360   --->   1 year, 0 months, 0 days
361   --->   1 year, 0 months, 1 day
362   --->   1 year, 0 months, 2 days
363   --->   1 year, 0 months, 3 days
364   --->   1 year, 0 months, 4 days
365   --->   1 year, 0 months, 0 days

Those are absurd results, and yet that is what would happen.

Now, if you were to deem a "year" to be 360 days, and a "month" to be 30 days, none of these anomalies occur (although you would now have the problem of your "year" not matching the actual length of a year...).

No, a year is 365 days, so you will get this:

360   --->   0 year, 12 months, 0 days
361   --->   0 year, 12 months, 1 day
362   --->   0 year, 12 months, 2 days
363   --->   0 year, 12 months, 3 days
364   --->   0 year, 12 months, 4 days
365   --->   1 year,  0 months,  0 days

I suggest using 365 days to be year BUT if 360 to 364 days are remaining after that, then that should be counted as a year also.  Otherwise, revert back to months and days.

Then:

359d      = 0y, 11m, 29d
360-365 = 1y, 0m, 0d
724d      =  1y, 11m, 29d
725-730  = 2y, 0m, 0d
etc.