Solved

Show datediff result in year and days

Posted on 2013-01-11
5
375 Views
Last Modified: 2013-01-12
Hi Experts

I am using below mentioned SQL query. The result of column DaysWithCompany=2859.
I need to display it into year and days like 7 year(s) 50 days or 0 year(s) 69 days (for new employee. Is there any function which can help me to get this result.

Select CC, EmpCode, EmpName, Name=Title+' '+EmpName, Designation, Department,
DaysWithCompany=Case When RegsinedOn is not null then DateDiff(day, JoiningDate, RegsinedOn)
                        else DateDiff(Day, JoiningDate, coalesce(RegsinedOn, getdate())) end
from Emp_Info a
Left Outer Join Emp_SalaryInfo b on a.TransNo=b.TransNoEmpInfo
where a.CC='01' and a.Empcode='00064'
0
Comment
Question by:Mehram
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38766866
exact representation in years and days will be tough...but you can get a approx representation...
like this...

Declare @Days int 
set @Days =2859 

select cast(@Days/365 as varchar(10))  + ' years and ' + cast(@Days%365 as varchar(10))  + ' Days' 

Open in new window


but it may fail for certain dates or for leap years...
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38767279
Did some searching around this morning and found this:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=170186

I've done some testing on it and this query seems to work.  The base logic he gives is this:

DECLARE @d1 DATETIME, @d2 DATETIME;

SET @d1 = '20070228';
SET @d2 = '20080301';

DECLARE @years INT, @months INT, @days INT;

SET @years = (CAST(CONVERT(CHAR(8),@d2,112) AS INT)
   -CAST(CONVERT(CHAR(8),@d1,112) AS INT))/10000;
SET @months = (DATEDIFF(MONTH,@d1,@d2)+12)%12 - CASE WHEN DAY(@d1)>DAY(@d2) THEN 1 ELSE 0 end;
SET @days = DATEDIFF(day,DATEADD(month,@months,DATEADD(YEAR,@years,@d1)),@d2);

Open in new window


There's an example in the post on doing it all with a CTE.  I don't have your data to work with and I am not a huge CTE expert but following his example something like this might be close.

Since you allow nulls in your table for dates, you'll probably have to do some other trapping in there for nulls in RegsinedOn (maybe replace references to RegsinedOn with your coalesce(RegsinedOn, getdate).

with CTE as
(
Select CC, 
       EmpCode, 
       EmpName, 
       Name=Title+' '+EmpName, 
       Designation, 
       Department,
       DaysWithCompany=Case When RegsinedOn is not null then DateDiff(day, JoiningDate, RegsinedOn)
                            else DateDiff(Day, JoiningDate, coalesce(RegsinedOn, getdate())) 
                       end,
       (CAST(CONVERT(CHAR(8),RegsinedOn,112) AS INT)
        -
        CAST(CONVERT(CHAR(8),JoiningDate,112) AS INT)
       )/10000 AS [Years],
       (DATEDIFF(MONTH,JoiningDate,RegsinedOn)+12)%12 
        - 
        CASE WHEN DAY(JoiningDate)>DAY(RegsinedOn) THEN 1 ELSE 0 END  AS [Months]       
from Emp_Info a
Left Outer Join Emp_SalaryInfo b on a.TransNo=b.TransNoEmpInfo
where a.CC='01' and a.Empcode='00064' 
)
SELECT	*,
	DATEDIFF(day,DATEADD(month,[Months],DATEADD(YEAR,[Years],JoiningDate)),ResignedOn) AS [Days]
FROM	cte;

Open in new window


See if that gets you started?
0
 

Author Comment

by:Mehram
ID: 38767569
I did something like this. Is it ok?

SELECT      Convert(Varchar(10), DATEDIFF(d, JoiningDate, coalesce(RegsinedOn,getdate()))/365) + ' Years ' +
            Convert(Varchar(10), (DATEDIFF(d,  JoiningDate, coalesce(RegsinedOn,getdate()))%365)/30) + ' Months ' +
            Convert(Varchar(10), (DATEDIFF(d,  JoiningDate, coalesce(RegsinedOn,getdate()))%365)%30) + ' Days '
FROM Emp_Info
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38767706
What you're doing is only going to give you an estimate - if you're OK with that, then I guess it's OK.

Some years have 366 days instead of 365.
Some months have 28, 29 or 31 days instead of 30.

Your results will be a close estimate (the further apart the date difference, the more inaccurate it will be).

The query I posted (not my query, borrowed from the forum I linked) seems to produce accurate results across leap years and varying month lengths.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 38768994
Here's my version of the code, which I believe is very accurate, as shown below (and doesn't require any CTE):


SELECT
    start_date,
    end_date AS end_date,
    --actual computations
    CAST(
        DATEDIFF(YEAR, start_date, end_date) - CASE WHEN SUBSTRING(start_date, 5, 4) > SUBSTRING(end_date, 5, 4) THEN 1 ELSE 0 END
    AS varchar(4)) + ' years ' +
    CAST(
        CASE WHEN SUBSTRING(start_date, 5, 4) > SUBSTRING(end_date, 5, 4)
            THEN 12 + CAST(SUBSTRING(end_date, 5, 2) AS int) - CAST(SUBSTRING(start_date, 5, 2) AS int)
            ELSE DATEDIFF(MONTH, start_date, end_date) % 12 END
        - CASE WHEN RIGHT(start_date, 2) > RIGHT(end_date, 2) THEN 1 ELSE 0 END
    AS varchar(2)) + ' months ' +
    CAST(
        CASE WHEN RIGHT(start_date, 2) > RIGHT(end_date, 2)
            THEN DATEDIFF(DAY, STUFF(CONVERT(char(8), DATEADD(MONTH, -1, end_date), 112), 7, 2, SUBSTRING(start_date, 7, 2)), end_date)
            ELSE CAST(RIGHT(end_date, 2) AS int) - CAST(RIGHT(start_date, 2) AS int)
        END    
    AS varchar(2)) + ' days '    
FROM
-- sample test dates
(
    SELECT '20050704' AS start_date, '20120921' AS end_date UNION ALL
    SELECT '20050922', '20120921' UNION ALL
    SELECT '20051008', '20120921' UNION ALL
    SELECT '20051022', '20120921' UNION ALL
    SELECT '20110228', '20120301' UNION ALL
    SELECT '20120228', '20120301' UNION ALL
    SELECT '20130228', '20130301'
) AS start_dates
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

617 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