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

Posted on 2008-01-25
Medium Priority
Last Modified: 2012-05-05
Hello Experts!!

Need a little help figuring out how to breakdown Number of Years and Remaining Days From a Grand Total Of Days Field.
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.
Question by:Tone' Shelby
  • 2
LVL 34

Accepted Solution

Brian Crowe earned 2000 total points
ID: 20746234
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

Author Comment

by:Tone' Shelby
ID: 20746575
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)

--- [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

Expert Comment

ID: 20746624
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


Author Comment

by:Tone' Shelby
ID: 20747443
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!

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

600 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