:::: MENU ::::

Get years, month and days difference togeather between two dates

  • Oct 22 / 2008
  • 4
Date and Time, dbDigger, T-SQL Scripts, T-SQL Tips and Tricks

Get years, month and days difference togeather between two dates

Suppose you have two dates and you are required to get difference in following form
00 years, 00 months and 00 days.
Normally we may get year, month, days etc alone through
datediff(yy/mm/dd, firstDate, secondDate) function. But in this case we are required to get Year, month and days difference is required together.
So get date in above format use following T-SQL script

 
SELECT '2007-04-25' as [FirstDate], getdate() as [SecondDate],  
 CONVERT(varchar(6),datediff(yy,'2007-04-25',getdate())) +  
 ' Years, '+  
 CONVERT(varchar(4),datediff(mm, '2007-04-25',getdate())%12)+  
 ' Months and '+  
 CONVERT(varchar(4),datediff(dd, '2007-04-25',getdate())%30)+  
 ' Days' as [RequiredDifference]  
 GO  
  • Anonymous

    This script does not work for all conditions. It calculates wrong for initial 6 months of year. I will further provide u example by script later.

  • Atif Shehzad

    Yes, i also noticed this. And soon i will update the script for a better one.

  • Atif Shehzad

    To get correct results up to months, we may use following

    SELECT '2007-04-25' as [FirstDate], getdate() as [SecondDate],
    CONVERT(varchar(6),datediff(mm, '2007-04-25',getdate())/12 )+
    ' Years, '+
    CONVERT(varchar(4),datediff(mm, '2007-04-25',getdate())%12)+
    ' Months' as [RequiredDifference]
    GO

  • Atif Shehzad

    For correct results up to months, we may use following

    SELECT '2007-04-25' as [FirstDate], getdate() as [SecondDate],
    CONVERT(varchar(6),datediff(mm, '2007-04-25',getdate())/12 )+
    ' Years, '+
    CONVERT(varchar(4),datediff(mm, '2007-04-25',getdate())%12)+
    ' Months' as [RequiredDifference]
    GO

Consult us to explore the Databases. Contact us