برای اینکه به ازا هر رکورد این تفاضل محاسبه بشه بهتر اینه که ابتدا تابعی بصورت زیر بنویسید
CREATE FUNCTION GetDiff
(
@dt DateTime,
@Now datetime
)
RETURNS varchar(max)
AS
BEGIN
Declare @Str varchar(max),
@Temp datetime,
@Year int,
@Month int,
@Day int,
@Hour int,
@Minute int,
@Second int
Set @Str = ''
Set @Year = DATEDIFF(Year, @dt, @Now) - 1
Set @Temp = DateAdd(Year, @Year, @dt)
Set @Month = DATEDIFF(Month, @Temp, @Now) - 1
Set @Temp = DateAdd(Month, @Month, @Temp)
Set @Day = DATEDIFF(Day, @Temp, @Now) - 1
Set @Temp = DateAdd(Day, @Day, @Temp)
Set @Hour = DATEDIFF(Hour, @Temp, @Now)
Set @Temp = DateAdd(Hour, @Hour, @Temp)
Set @Minute = DATEDIFF(Minute, @Temp, @Now)
Set @Temp = DateAdd(Minute, @Minute, @Temp)
Set @Second = DATEDIFF(Second, @Temp, @Now)
Set @Temp = DateAdd(Second, @Second, @Temp)
if(@Second > 59)
Begin
Set @Minute = @Minute + 1
Set @Second = @Second % 60
End
if(@Minute > 59)
Begin
Set @Hour = @Hour + 1
Set @Minute = @Minute % 60
End
if(@Hour > 23)
Begin
Set @Day = @Day + 1
Set @Hour = @Hour % 24
End
if(@Month IN (0,2,4,6,7,9,11) AND (@Day > 30))
Begin
Set @Month = @Month + 1
Set @Day = @Day % 31
End
else if(@Month IN (3,5,8,10) AND (@Day > 29))
Begin
Set @Month = @Month + 1
Set @Day = @Day % 30
End
else if (@Month = 1 AND @Day > 27)
Begin
Set @Month = @Month + 1
Set @Day = @Day % 28
End
if(@Month > 11)
Begin
Set @Year = @Year + 1
Set @Month = @Month % 12
End
Set @Str = @Str + Case When @Year > 0
Then Cast(@Year as varchar) + ' Year ' Else '' End
Set @Str = @Str + Case When @Month > 0 OR @Str <> ''
Then Cast(@Month as varchar) + ' Month ' Else '' End
Set @Str = @Str + Case When @Day > 0 OR @Str <> ''
Then Cast(@Day as varchar) + ' Day ' Else '' End
Set @Str = @Str + Case When @Hour > 0 OR @Str <> ''
Then Cast(@Hour as varchar) + ' Hour ' Else '' End
Set @Str = @Str + Case When @Minute > 0 OR @Str <> ''
Then Cast(@Minute as varchar) + ' Minute ' Else '' End
Set @Str = @Str + Case When @Second > 0 OR @Str <> ''
Then Cast(@Second as varchar) + ' Second ' Else '' End
Return @Str
END
بعد توی دستور Select بصورت زیر عمل کنید
Select dbo.GetDiff(date, GetDate()) as DateDiff From tblajax