asked on

SQL Syntax to Take Calculate Total Years And Remaing Days From A Grand Total Days Field....

Hello Experts!!

Need a little help figuring out how to breakdown Number of Years and Remaining Days From a Grand Total Of Days Field.
Example:
I have a [Total Exp Days] field with a total of 1190 days.

I want to be able to Show the Years and Days in seperate fields in the Query: [Exp Years], [Exp Days]

For example 1190 days to bring me back 4 years and 95 days in two seperate fields.

Thank You.

ASKER CERTIFIED SOLUTION

Brian Crowe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tone' Shelby

ASKER

Thank You! You were right on target! .... My mistake yes it is 3 (bad typo on my part) ...Your solution took me in the right direction by adding the sum (as I had mutiples to add uo with a SUM)

SELECT
--- [Total Exp Days] is 1190 and to break it down
SUM([Total Exp Days] )/ 365 AS [Exp Years]
, SUM([Total Exp Days]) % 365 AS [Exp Days]

From MyTable

fesnyng

You do not state what the '1190 days' is. For example, is it simply an approximation of how many years and days would 1190 days be? If so, then BriCrowe has a very good, and much simpler, answer. However, if it is the exact duration until a future date or the duration since a past date, then leap years must be accounted for.

The attached code snippet uses MS SQL Server date functions. It calculates duration to a future date as years and days accounting for the 2008 leap year.

Counting forward from today, 1190 days is 3 years and 94 days.

-- input
declare @totaldays int -- how many days
declare @startdate datetime -- when to count from
-- calculate
declare @date_totaldays  datetime  -- @startdate + @totaldays
declare @date_years datetime  -- @startdate + @years
-- results
declare @years int -- how many years 
declare @days int -- how many days remainder  
 
 
-- future dates (similar process for past dates)
-- account for arbitrary leap years
-- @date_totaldays = future date based on total days
-- @date_years = future date based on year component of @date_totaldays
 
-- @years = year of @date_totaldays minus year of @startdate
-- @days = remainder, i.e. difference between future dates
 
-- in
set @totaldays = 1190
set @startdate = getdate()
 
-- get year
set @date_totaldays = DATEADD (day , @totaldays, @startdate )
set @years = YEAR(@date_totaldays) - YEAR (getdate()) -- years to date
 
-- get day remainder
set @date_years = DATEADD (year, @years , @startdate )
set @days = DATEDIFF (day, @date_years, @date_totaldays) -- days 
 
-- result
select 'Results:', @years, @days

Open in new window

Tone' Shelby

ASKER

Oh Thank You. This is very good information. Yes the 1190 was a hard number and all that was needed was a approximation as it was a number of total past days a person filled a particular role.

I thank you for sharing what you did becuse I may need that information in the future as well.

Thanks Again!