Home » » How do I subtract two date columns and two time columns in sql

How do I subtract two date columns and two time columns in sql

I have 4 columns that are
Startdate, 
enddate, 
starttime, 
endtime.  
I need to get subtractions from the enddate and endtime - startdate and starttime. I will need that answer from the four columns in sql.
so far I have this I found but dont think this will work right.
SELECT DATEDIFF (day, enddate, startdate) as NumberOfDays  
DATEDIFF(hour,endtime,starttime) AS NumberOfHours 
DATEDIFF(minute,endtime,starttime) AS NumberOfMinutes 
from table;
Thanks for your help
----Answer----
1.
Assuming you have data like this, you can add the StartDate to the StartTime to get the StartDateTime, same for the EndDateTime
StartDate                StartTime                EndDate                  EndTime              
-----------------------  -----------------------  -----------------------  -----------------------
2014-05-01 00:00:00.000  1900-01-01 10:53:28.290  2014-05-07 00:00:00.000  1900-01-01 11:55:28.290
Once you've done that you can get the Days, Hours and Minutes like this:
select 
DATEDIFF(minute, StartDate + StartTime, EndDate + EndTime) / (24*60) 'Days',
(DATEDIFF(minute, StartDate + StartTime, EndDate + EndTime) / 60) % 24 'Hours',
DATEDIFF(minute, StartDate + StartTime, EndDate + EndTime) % 60 'Minutess'
  from YourTable
We have work in minutes the whole time in order to prevent problems with partial days crossing midnight and partial hours crossing an hour mark.
2.
EDIT - Now that I realize that the question is for SQL Server 2000, this proposed answer may not work.
The SQL Server 2000 documentation can be found at https://www.microsoft.com/en-us/download/details.aspx?id=18819. Once installed, look for tsqlref.chm in your installed path, and in that help file you can find information specific to DATEDIFF.

Based on the wording of the original question, I'm assuming that the start/end time columns are of type TIME, meaning there is no date portion. With that in mind, the following would answer your question.
However, note that depending on your data, you will lose precision in regards to the seconds and milliseconds.
More about DATEDIFF: https://msdn.microsoft.com/en-us/library/ms189794.aspx

DECLARE @mytable AS TABLE 
    (
        startdate DATETIME,
        enddate DATETIME,
        starttime TIME,
        endtime TIME
    )


INSERT INTO @mytable (startdate, enddate, starttime, endtime)
VALUES      (GETDATE() - 376, GETDATE(), '00:00:00', '23:59')

SELECT      *
FROM        @mytable

SELECT      DATEDIFF(HOUR, startdate, enddate) AS [NumHours],
            DATEDIFF(MINUTE, starttime, endtime) AS [NumMinutes]
FROM        @mytable
This would yield output similar to:
enter image description here


0 nhận xét:

Post a Comment

Popular Posts

Powered by Blogger.