Solved

# Derive Years & Months based upon days

Posted on 2012-08-14
372 Views
Last Modified: 2012-09-30
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.
0
Question by:vikas_nm
13 Comments

LVL 92

Expert Comment

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 Comment

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

LVL 92

Expert Comment

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

LVL 4

Expert Comment

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

LVL 92

Expert Comment

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

LVL 4

Assisted Solution

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 Comment

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

LVL 92

Expert Comment

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 Comment

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

LVL 92

Expert Comment

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

LVL 68

Expert Comment

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

LVL 68

Expert Comment

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

LVL 68

Accepted Solution

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

## Join & Write a Comment Already a member? Login.

### Suggested Solutions

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

#### 746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!