I have 4 columns that are
so far I have this I found but dont think this will work right.
----Answer----
1.
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
2.
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
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:
0 nhận xét:
Post a Comment