DATE DIFF QUERY IN SQL SERVER

Question

I AM HAVING A TABLE NAME PERSONAL  IN A TABLE I AM HAVING TWO COLUMNS NAMED DATE OF BIRTH (DOE) AND DATE OF ENROLLMENT NOW I WANT TO TAKE OUT ACTUAL DIFFERENCE BETWEEN THE TWO COLUMNS IN YEARS MONTHS AND DATE. FOR EXAMPLE DOB DOE ANSWER SHOULD BE LIKE THIS 09 FEB 1981 26 SEP 2003 09YRS 03MTS 12DAYS SOMEBODY HELP ME OUT OF THIS PLZ AN EARLY ANSWER WILL BE HIGHLY APPRECIATED!!!!!!!!!!!!!!!!!

Santhosh Kumar Jayaraman · Answer

i have tried with some test values and its working. declare @string varchar(20)='09 apr 1981' declare @string2 varchar(20)='30 Mar 2012' declare @month varchar(40)='JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC' select Case When (((CHARINDEX(substring(@string243)@month0)-1)/3 +1)-(CHARINDEX(substring(@string43)@month0)-1)/3 +1>=0) then cast(cast(Substring(@string284) as int)-cast(Substring(@string84) as int) as varchar(20)) else cast((cast(Substring(@string284) as int)-cast(Substring(@string84) as int))-1 as varchar(20)) end + 'YRS ' + Case When (((CHARINDEX(substring(@string243)@month0)-1)/3 +1)-((CHARINDEX(substring(@string43)@month0)-1)/3 +1)>=0 ) then cast(((CHARINDEX(substring(@string243)@month0)-1)/3 +1)-((CHARINDEX(substring(@string43)@month0)-1)/3 +1)-1 as varchar(20)) else cast(12+((CHARINDEX(substring(@string243)@month0)-1)/3 +1)-((CHARINDEX(substring(@string43)@month0)-1)/3 +1)-1 as varchar(20)) End + 'MTS '+ Case When (cast(Substring(@string212) as int)-cast(Substring(@string12) as int)>=0) then cast(cast(Substring(@string212) as int)-cast(Substring(@string12) as int) as varchar(20)) else cast(30+(cast(Substring(@string212) as int)-cast(Substring(@string12) as int)) as varchar(20)) end + 'DAYS ' i Considered 30 days as month count

Sudhakar Chaudhary · Answer

SELECT DATEDIFF(YEARDOBDOE) AS YEARSDATEDIFF(MONTHDOBDOE) AS MONTHS  (DATEDIFF(DAYDOBDOE)-(DATEDIFF(MONTHDOBDOE)*30) )AS DAYS FROM SPT