Click here to Skip to main content
15,867,330 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Using below query i am getting output in this way

select empid, cast(floor(experience / 365) as varchar) + ' years ' +
              cast(floor(experience % 365 / 30) as varchar) + ' months ' +
              cast(experience % 30 as varchar) + ' days' as experience
from (select *, datediff(DAY, doj, getdate()) as experience
      from employee) t



but i need years months and days which was done but instead of getting all the days i need to get only the remaining days ie between 1 to 30 or 31

What I have tried:

i had tried the above code which is not working
Posted
Updated 20-Apr-17 21:19pm
v4
Comments
CHill60 21-Apr-17 4:11am    
I see you have changed the query that you are using but it still doesn't work. Why not try Solution 2 - the second part, that actually does do what you want it to do?

You need to subtract the number of days that is taken by the whole months. But anyway it's not precise because you assume every month has 30 days. This is how I would do it:
SQL
declare @date as date = '20150106'
declare @now as date = GetDate()
declare @years as int = DATEDIFF(year, @date, @now) -- full years
declare @date2 as date = dateadd(year, @years, @date) -- date moved by # of years
declare @months as int = DATEDIFF(month, @date2, @now) -- full months since the new date
declare @date3 as date = dateadd(month, @months, @date2) -- date moved by # of months
declare @days as int = DATEDIFF(day, @date3, @now) -- full days since the new date
select @years as [Years], @months as [Months], @days as [Days]

This is done step by step on purpose so you can follow what is happening. When I calculate the number of years for example I move the date by the same time interval and in next step I use this modified date. This can be easily inlined to form a single statement but would be extremely hard to read.
Edit
The solution above does only work in some situations. It actually doesn't work for 1-Jun-2016. Here is the updated version:
SQL
declare @date as date = '20150601'
declare @now as date = GetDate()
declare @yearOffset as int = case when month(@date) < month(@now) or (month(@date) = month(@now) and day(@date) <= day(@now)) then 0 else -1 end
declare @years as int = DATEDIFF(year, @date, @now) + @yearOffset
declare @date2 as date = dateadd(year, @years, @date)
declare @monthOffset as int = case when day(@date) <= day(@now) then 0 else -1 end
declare @months as int = DATEDIFF(month, @date2, @now) + @monthOffset
declare @date3 as date = dateadd(month, @months, @date2)
declare @days as int = DATEDIFF(day, @date3, @now)
select @years as [Years], @months as [Months], @days as [Days]
 
Share this answer
 
v2
Comments
CHill60 20-Apr-17 8:52am    
I actually got 2 yrs 3 months and 14 days - it is because the date is open to interpretation and because the DATEDIFF(year, @date, @now) returns the result of 2017-2015
Tomas Takac 20-Apr-17 8:58am    
I get 2y 3m 14d which I think is correct assuming @date=6-Jan-2015 and @now=20-Apr-2017.
CHill60 20-Apr-17 9:34am    
I read the OP's date as 1st June - which fits with their expected results.
Tomas Takac 20-Apr-17 9:56am    
I see, this won't work then...
Tomas Takac 20-Apr-17 10:09am    
I updated the solution, have a look.
As an alternative to Solution 1 (but note it is, as warned, extremely hard to read):
SQL
declare @date as date = '2015-06-01'

SELECT  CAST(DATEDIFF(day, @date,GETDATE()) / 365 AS varchar) + ' Years ' + 
		CAST(CAST(DATEDIFF(day, @date, GETDATE()) % 365 / 30.5 AS int) AS varchar) + ' Months ' +
		CAST(DATEDIFF(DAY, DATEADD(MONTH, CAST(DATEDIFF(day, @date, GETDATE()) % 365 / 30.5 AS int), 
				DATEADD(YEAR, CAST(DATEDIFF(day, @date,GETDATE()) / 365 AS int), @date)), GETDATE()) AS varchar) + ' Days'
Principle is the same, remove the number of days from the years and months already calculated to leave the remaining days.
This is not a good approach though - it assumes all years have 365 days and uses the "average" number of days in a month of 30.5 (which is only slightly more accurate than using 30).
There will be specific dates where this calculation is just wrong.

[EDIT] The following gives the correct result and should be accurate regardless of when run. It is still as difficult to read:
SQL
declare @start as date = '2015-Jun-01'
declare @end as Date = getdate()

select @start, CAST(DATEDIFF(MONTH, @start, @end) / 12 AS varchar) + ' Years ' + 
	CAST(DATEDIFF(MONTH, DATEADD(YEAR, DATEDIFF(MONTH, @start, @end) / 12, @start), @end) as varchar) + ' Months ' + 
	CAST(DATEDIFF(DAY, DATEADD(MONTH, DATEDIFF(MONTH, DATEADD(YEAR, DATEDIFF(MONTH, @start, @end) / 12, @start), @end), DATEADD(YEAR,DATEDIFF(MONTH, @start, @end) / 12,@start)), @end) AS varchar) + ' Days'
 
Share this answer
 
v2
SELECT CAST(DATEDIFF(yy, day, GETDATE()) AS varchar(2)) + ' Years '+
       CAST(DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, day, GETDATE()), day), GETDATE()) AS varchar(2)) +' Month '+
       CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, day, GETDATE()), day), GETDATE()), DATEADD(yy, DATEDIFF(yy, day, GETDATE()), day)),  GETDATE()) AS varchar(2)) +' Days ' AS Expr1
from records;
 
Share this answer
 
Comments
CHill60 20-Apr-17 9:30am    
Results in 2 years -2 months and 19 days

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900