Mehram
asked on
Show datediff result in year and days
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'
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'
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:
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).
See if that gets you started?
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);
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;
See if that gets you started?
ASKER
I did something like this. Is it ok?
SELECT Convert(Varchar(10), DATEDIFF(d, JoiningDate, coalesce(RegsinedOn,getdat e()))/365) + ' Years ' +
Convert(Varchar(10), (DATEDIFF(d, JoiningDate, coalesce(RegsinedOn,getdat e()))%365) /30) + ' Months ' +
Convert(Varchar(10), (DATEDIFF(d, JoiningDate, coalesce(RegsinedOn,getdat e()))%365) %30) + ' Days '
FROM Emp_Info
SELECT Convert(Varchar(10), DATEDIFF(d, JoiningDate, coalesce(RegsinedOn,getdat
Convert(Varchar(10), (DATEDIFF(d, JoiningDate, coalesce(RegsinedOn,getdat
Convert(Varchar(10), (DATEDIFF(d, JoiningDate, coalesce(RegsinedOn,getdat
FROM Emp_Info
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
like this...
Open in new window
but it may fail for certain dates or for leap years...