How can I get the value of the date time difference in SQL Server

Does anyone know how can I get the value of the date time difference in SQL Server?

For example:

If I have a table call customer with the following column

Name    Join_Date             Exp_Date 
Alvin   2010-01-01 12:30:00   2010-01-03 11:30:00 
Amy     2010-01-01 15:30:00   2010-01-02 10:30:00

How can I get the following result in SQL Server?

Name    Join_Date             Exp_Date                Diff
Alvin   2010-01-01 12:30:00   2010-01-03 11:30:00开发者_如何学Go     2days 1hour
Amy     2010-01-01 15:30:00   2010-01-02 10:30:00     1day 7hours

Select Name, Join_Date, Exp_Date
    , DateDiff(d, Join_Date, Exp_Date) As [Days]
    , DateDiff(hh, Join_Date, Exp_Date) As [Hours]

Or

Select Name, Join_Date, Exp_Date
    , Cast( DateDiff(d, Join_Date, Exp_Date) As varchar(10) ) + ' days ' 
        + Cast( DateDiff(hh, Join_Date, Exp_Date)  As varchar(10) ) + ' hours'

Or the super fancy version

Select Name, Join_Date, Exp_Date
    , Cast( DateDiff(d, Join_Date, Exp_Date) As varchar(10) ) 
        + Case
            When DateDiff(d, Join_Date, Exp_Date) = 1 Then ' day '
            Else ' days '
            End
        + Cast( DateDiff(hh, Join_Date, Exp_Date)  As varchar(10) ) 
        + Case
            When DateDiff(hh, Join_Date, Exp_Date) = 1 Then ' hour'
            Else ' hours '
            End

use DATEDIFF: http://msdn.microsoft.com/en-us/library/aa258269(v=sql.80).aspx

My solution assumes that you want date/time part names in English and uses a UDF.

It calculates the actual difference between datetimes, i.e. if Exp_Date is today and Join_Date was yesterday but it is less than 24 hours between them, then the number of days will be 0. Nil parts are not displayed.

CREATE FUNCTION dbo.fnGetDTPart (@UnitName varchar(10), @Number int)
RETURNS varchar(50)
AS BEGIN
  DECLARE @Result varchar(50);
  IF @Number = 0
    SET @Result = ''
  ELSE
    SET @Result = CAST(@Number AS varchar) + @UnitName +
      CASE @Number WHEN 1 THEN ' ' ELSE 's ' END;
  RETURN @Result;
END
GO


WITH Customer (Name, Join_Date, Exp_Date) AS (
  SELECT
    'Alvin',
    CAST('2010-01-01 12:30:00' AS datetime),
    CAST('2010-01-03 11:30:00' AS datetime)
  UNION
  SELECT
    'Amy',
    CAST('2010-01-01 15:30:00' AS datetime),
    CAST('2010-01-02 10:30:00' AS datetime)
)
SELECT
  Name,
  Join_Date,
  Exp_Date,
  Diff =
    dbo.fnGetDTPart('year',   DATEPART(year,   DiffDT)-1900) +
    dbo.fnGetDTPart('month',  DATEPART(month,  DiffDT)-1) +
    dbo.fnGetDTPart('day',    DATEPART(day,    DiffDT)-1) +
    dbo.fnGetDTPart('hour',   DATEPART(hour,   DiffDT)) +
    dbo.fnGetDTPart('minute', DATEPART(minute, DiffDT)) +
    dbo.fnGetDTPart('second', DATEPART(second, DiffDT))
FROM (
  SELECT
    Name,
    Join_Date,
    Exp_Date,
    DiffDT = Exp_Date - Join_Date
  FROM Customer
) s

Assuming you want the actual correct date difference (as described in Andriy M's comment) and are just looking to display it in a user friendly way, you could do this:

select  Join_Date
        ,Exp_Date
        ,cast((datediff(hour,Join_Date,Exp_Date) / 24) as nvarchar) + 'days ' 
        + cast((datediff(hour,Join_Date,Exp_Date) % 24) as nvarchar) + 'hours' as Diff