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-MM-DD ') 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-mm-dd '))/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 all 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 obtained 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