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.
vikas_nmAsked:
Who is Participating?
 
Scott PletcherSenior 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
 
Patrick MatthewsCommented:
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
 
vikas_nmAuthor Commented:
Considered 365 days as 1 year and 30 days as 1 month.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Patrick MatthewsCommented:
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 :)
0
 
DustinKikuchiCommented:
Are you using PHP or another language to format the output or do you need the result strictly from a SQL query?
0
 
Patrick MatthewsCommented:
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

Open in new window


Assumes BillDays is of type int, bigint, or smallint.
0
 
DustinKikuchiCommented:
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

Open in new window

0
 
vikas_nmAuthor 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
 
Patrick MatthewsCommented:
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
 
vikas_nmAuthor 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
 
Patrick MatthewsCommented:
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
 
Scott PletcherSenior 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
 
Scott PletcherSenior 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.

All Courses

From novice to tech pro — start learning today.