DATE DIFF QUERY IN SQL SERVER

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

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(@string2,4,3),@month,0)-1)/3 +1)-(CHARINDEX(substring(@string,4,3),@month,0)-1)/3 +1>=0)

then cast(cast(Substring(@string2,8,4) as int)-cast(Substring(@string,8,4) as int) as varchar(20))

else cast((cast(Substring(@string2,8,4) as int)-cast(Substring(@string,8,4) as int))-1 as varchar(20)) end + 'YRS ' +

Case When (((CHARINDEX(substring(@string2,4,3),@month,0)-1)/3 +1)-((CHARINDEX(substring(@string,4,3),@month,0)-1)/3 +1)>=0 )

then cast(((CHARINDEX(substring(@string2,4,3),@month,0)-1)/3 +1)-((CHARINDEX(substring(@string,4,3),@month,0)-1)/3 +1)-1 as varchar(20))

else cast(12+((CHARINDEX(substring(@string2,4,3),@month,0)-1)/3 +1)-((CHARINDEX(substring(@string,4,3),@month,0)-1)/3 +1)-1 as varchar(20)) End

+ 'MTS '+

Case When (cast(Substring(@string2,1,2) as int)-cast(Substring(@string,1,2) as int)>=0)

then cast(cast(Substring(@string2,1,2) as int)-cast(Substring(@string,1,2) as int) as varchar(20))

else cast(30+(cast(Substring(@string2,1,2) as int)-cast(Substring(@string,1,2) as int)) as varchar(20)) end + 'DAYS '

i Considered 30 days as month count

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Santhosh Kumar Jayaraman14y ago
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(@string2,4,3),@month,0)-1)/3 +1)-(CHARINDEX(substring(@string,4,3),@month,0)-1)/3 +1>=0)
then cast(cast(Substring(@string2,8,4) as int)-cast(Substring(@string,8,4) as int) as varchar(20))
else cast((cast(Substring(@string2,8,4) as int)-cast(Substring(@string,8,4) as int))-1 as varchar(20)) end + 'YRS ' +
Case When (((CHARINDEX(substring(@string2,4,3),@month,0)-1)/3 +1)-((CHARINDEX(substring(@string,4,3),@month,0)-1)/3 +1)>=0 )
then cast(((CHARINDEX(substring(@string2,4,3),@month,0)-1)/3 +1)-((CHARINDEX(substring(@string,4,3),@month,0)-1)/3 +1)-1 as varchar(20))
else cast(12+((CHARINDEX(substring(@string2,4,3),@month,0)-1)/3 +1)-((CHARINDEX(substring(@string,4,3),@month,0)-1)/3 +1)-1 as varchar(20)) End
+ 'MTS '+
Case When (cast(Substring(@string2,1,2) as int)-cast(Substring(@string,1,2) as int)>=0)
then cast(cast(Substring(@string2,1,2) as int)-cast(Substring(@string,1,2) as int) as varchar(20))
else cast(30+(cast(Substring(@string2,1,2) as int)-cast(Substring(@string,1,2) as int)) as varchar(20)) end + 'DAYS '

i Considered 30 days as month count
0
Sudhakar Chaudhary14y ago
SELECT DATEDIFF(YEAR,DOB,DOE) AS YEARS,DATEDIFF(MONTH,DOB,DOE) AS MONTHS , (DATEDIFF(DAY,DOB,DOE)-(DATEDIFF(MONTH,DOB,DOE)*30) )AS DAYS FROM SPT
0