CODEWITHSUNDEEP
sqlsql-serversql-server-2000datediffsubtraction
user3537618
user3537618

Reputation: 21

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

Upvotes: 2

Views: 37385

Answers (2)

dev8675309
dev8675309

Reputation: 396

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

Upvotes: 1

Jeremy Hutchinson
Jeremy Hutchinson

Reputation: 2045

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.

Upvotes: 0

Related Questions