SQL Date Time Subtraction statement

Source: Internet
Author: User
Keywords Network programming Database synthesis
Tags date difference example function how to network network programming programming

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.