SQL Date Time Subtraction statement

SQL Date Time Subtraction statement
This tutorial takes advantage of the DateDiff function to subtract the date of the database tutorial Oh, the following is written n about the Mssql date subtraction method.
Subtract two dates in SQL
1, Days of difference
Select Trunc (sysdate, ' yyyy ')-to_date (' 2009-01-04 ', ' yyyy ') from dual;
2, the difference between the number of months
Select Months_between (trunc (sysdate, ' mm '), to_date (' 2009-01 ', ' yyyy-mm ')) from dual;
3. The difference of years
Select Trunc ((Months_between (Trunc (sysdate, ' DD '), to_date (' 2009-01-01 ', ' yyyy '))/12) from dual;

4, select DateDiff (Day, ' 2008.08.25 ', ' 2008.09.01 ')

5, select DateDiff (Second, 2009-8-25 12:15:12 ', 2009-9-1 7:18:20 ')--Returns the difference of seconds
6,
Select DateDiff (minute, 2009-8-25 12:15:12 ', 2009-9-1 7:18:20 ')--Returns the difference of minutes
7,
Select DateDiff (Hour, 2009-8-25 12:15:12 ', 2009-9-1 7:18:20 ')--Returns the difference of hours

Question three: Select DateDiff (Day, 2009-8-25 12:15:12 ', 2009-9-1 7:18:20 ')

Example Two

Use pubs
SELECT DISTINCT DateDiff (day, ' 2009-3-12 ', ' 2009-3-15 ') as Difday
From titles
Results: 3

Declare @dt1 as DateTime, @dt2 as DateTime;
Select @dt1 = ' 2008-8-4 9:36:41 ', @dt2 = ' 2008-8-2 9:33:39 ';

declare @days as int, @hours as int, @minutes as int, @seconds as int;

Set @seconds = DateDiff (Second, @dt2, @dt1);
Set @days = @seconds/(24 * 60 * 60)
Set @seconds = @seconds-@days * 24 * 60 * 60
Set @hours = @seconds/(60 * 60);
Set @seconds = @seconds-@hours * 60 * 60
Set @minutes = @seconds/60;
Set @seconds = @seconds-@minutes * 60;
Select CONVERT (varchar, @days) + ' days ' + convert (varchar, @hours) + ' hours ' + convert (varchar), @minutes) + ' min ' + CONVERT (varchar), @seconds) + ' seconds ';

Let's take a look at an example

I have a table with four fields: Start days, start times, arrival days, arrival times (these four fields are varchar types)

For example: A record: 1 16:00 2 12:20

My purpose is to use Select (Arrival days + arrival time)-(Start days + start time) as to spend time from table

For example, the previous record is (2*24:00+12:20)-(24:00+16:00) =20:00

How do you write such an SQL statement???

DECLARE @t table
(
Beginday int,
BeginTime varchar (20),
Endday int,
Endtime varchar (20)
)

Insert @t Select 1, ' 16:00 ', 2, ' 12:20 '
UNION ALL Select 1, ' 3:00 ', 3, ' 19:10 '

Select
Date=rtrim (DATE/60) + ': ' +rtrim (date%60)
From
(Select Date=datediff (Mi,1,dateadd (d,endday-beginday,beginday)-begintime+endtime) from @t) t

Date
-------------------------
20:20
64:10

Method Two

Declare @t table (Start days varchar (10), Start time varchar (10), Arrival days varchar (10), Arrival time varchar (10))
Insert @t Select ' 1 ', ' 16:00 ', ' 2 ', ' 12:20 '

--If the start days, the arrival days are greater than 31
Select arrival Days * + DATEPART (hh, arrival time)-Start days * 24-datepart (hh, start time)
From @t