?
Solved

Derive Years & Months based upon days

Posted on 2012-08-14
13
Medium Priority
?
426 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
Comment
Question by:vikas_nm
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38293111
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

by:vikas_nm
ID: 38293141
Considered 365 days as 1 year and 30 days as 1 month.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38293176
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 4

Expert Comment

by:DustinKikuchi
ID: 38293184
Are you using PHP or another language to format the output or do you need the result strictly from a SQL query?
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38293197
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
 
LVL 4

Assisted Solution

by:DustinKikuchi
DustinKikuchi earned 750 total points
ID: 38293234
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
 

Author Comment

by:vikas_nm
ID: 38293245
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 93

Expert Comment

by:Patrick Matthews
ID: 38293266
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

by:vikas_nm
ID: 38293273
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 93

Expert Comment

by:Patrick Matthews
ID: 38293803
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 70

Expert Comment

by:Scott Pletcher
ID: 38298247
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 70

Expert Comment

by:Scott Pletcher
ID: 38298266
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 70

Accepted Solution

by:
Scott Pletcher earned 750 total points
ID: 38298283
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

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

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

Join & Ask a Question