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.
LVL 9
Tone' ShelbyMicrosoft® Power BI, Power Apps & O365 Design, DevelopmentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
Your example and description seem to conflict as the number of complete years in 1190 days is 3 not 4.  Try the query below.

SELECT [Total Exp Days] / 365 AS [Exp Years], [Total Exp Days] % 365 AS [Exp Days]
FROM myTable
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tone' ShelbyMicrosoft® Power BI, Power Apps & O365 Design, DevelopmentAuthor Commented:
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
0
fesnyngCommented:
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

0
Tone' ShelbyMicrosoft® Power BI, Power Apps & O365 Design, DevelopmentAuthor Commented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.