# Derive Years & Months based upon days

Hi,

Experts I have got a table that contains Id, CustomerName & BillDays as the columns. I want to derive the number of years and months and days from the Bill Days for each each row in the table.
###### Who is Participating?

Senior DBACommented:
Or, in "SQLese":

BillYrs AS BillDays / 365 + CASE WHEN BillDays % 365 BETWEEN 360 AND 364 THEN 1 ELSE 0 END,

BillMths AS BillDays % 365 + CASE WHEN BillDays % 365 BETWEEN 360 AND 364 THEN 0 ELSE BillDays % 365 / 30 END,

BillDays AS BillDays % 365 + CASE WHEN BillDays % 365 BETWEEN 360 AND 364 THEN 0 ELSE BillDays % 365 % 30 END
0

Commented:
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?
0

Author Commented:
Considered 365 days as 1 year and 30 days as 1 month.
0

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

1 year, 12 months, 4 days

0

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

Commented:
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
``````

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

Commented:
You will likely want to FLOOR() your values as well:

i.e.

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

Author Commented:
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.
0

Commented:
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
0

Author Commented:
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
0

Commented:
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...).
0

Senior DBACommented:
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
0

Senior DBACommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.