August 30, 2016 at 12:12 pm
Hello,
I have a problem where i want to get the diffrence in minutes between two times, my datatype is TIME, i have also used the function below
ABS(DATEDIFF (MINUTE, cd.StartTime , p.StartTime)) AS MinuteDiff.
the problem is, where i have a cd.startime of 19:00:00 and a p.startime of 00:00:00, it returns 1140 minutes (19hrs) as the minute difference, how can i get the minute difference appropriately as (300 minutes=5hrs) this is the appropriate difference between 19:00 and 00:00(being 12 am).
I learn from the footprints of giants......
August 30, 2016 at 1:42 pm
JALLYKAMOZE (8/30/2016)
Hello,I have a problem where i want to get the diffrence in minutes between two times, my datatype is TIME, i have also used the function below
ABS(DATEDIFF (MINUTE, cd.StartTime , p.StartTime)) AS MinuteDiff.
the problem is, where i have a cd.startime of 19:00:00 and a p.startime of 00:00:00, it returns 1140 minutes (19hrs) as the minute difference, how can i get the minute difference appropriately as (300 minutes=5hrs) this is the appropriate difference between 19:00 and 00:00(being 12 am).
The DATEDIFF function takes dates into account, so you'll need to bring the appropriate date column in the calculation.
August 30, 2016 at 3:23 pm
I don't believe you are taking the date into consideration.
Midnight (00:00) is treated as the start of the day.
If the 19:00 time is on the same date as the 00:00 time, then the correct answer IS 19 hours.
If the 19:00 time is on the date BEFORE the 00:00 time, then you will get the 300 hour answer you want.
declare @sampleDates table (cdStartTime datetime, pStartTime datetime)
insert into @sampleDates values
('1/1/2016 00:00', '1/1/2016 19:00'),
('1/2/2016 00:00', '1/1/2016 19:00')
Select cdStartTime, pStartTime,
ABS(DATEDIFF (MINUTE, cdStartTime , pStartTime)) AS MinuteDiff
from @sampleDates
Edited to add: You can't do time calculations that span days without taking the dates into account. SQL does this for you with DATEDIFF, but you have to feed it datetime values instead of just time values. With time values only, all it's delivering is the absolute value of 19-0 or 0-19.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 30, 2016 at 4:50 pm
if you're sure that no even would ever take longer than 24 hours then you can use this trick:
SELECT (24*60 + DATEDIFF (MINUTE, '19:00' , '00:00'))%(24*60)
But anyway, I'd recommend you to follow the advices above.
_____________
Code for TallyGenerator
August 31, 2016 at 5:12 am
Quick suggestion, shift both the start and end time back 3 milliseconds when the end time is 00:00:00
😎
USE TEEST;
GO
SET NOCOUNT ON;
-- Calculating the difference in minutes between two time stamps
-- and adjusting the end time if it is exactly midnight by shifting
-- both the start and end time by 3 milliseconds.
-- The assumption is that both time stamps fall on the same day as
-- otherwise this calculation is wrong as only the time is provided.
-- Sample data set
;WITH SAMPLE_DATA
(
DT_ID -- INT
,CD_START_TIME -- TIME(5)
,P_START_TIME -- TIME(5)
) AS
(
SELECT
DT_ID
,CONVERT(TIME(5), CD_START_TIME ,0) AS CD_START_TIME
,CONVERT(TIME(5), P_START_TIME ,0) AS P_START_TIME
FROM
(VALUES (1,'00:00:00','19:00:00')
,(2,'19:00:00','00:00:00')
,(3,'06:00:00','00:00:00')
,(4,'03:00:00','00:00:00')
,(5,'23:00:00','00:00:00')
) AS X(DT_ID,CD_START_TIME,P_START_TIME)
)
SELECT
SD.DT_ID
,SD.CD_START_TIME
,SD.P_START_TIME
,DATEDIFF(MINUTE,SD.CD_START_TIME,SD.P_START_TIME) AS NORMAL_DATE_DIFF
,DATEDIFF(MINUTE
,CASE
WHEN SD.P_START_TIME = CONVERT(TIME(5),'00:00:00',0) THEN DATEADD(MILLISECOND,-3,SD.CD_START_TIME)
ELSE SD.CD_START_TIME
END
,CASE
WHEN SD.P_START_TIME = CONVERT(TIME(5),'00:00:00',0) THEN DATEADD(MILLISECOND,-3,SD.P_START_TIME)
ELSE SD.P_START_TIME
END
) AS ADJUSTED_DATE_DIFF
FROM SAMPLE_DATA SD;
Output
DT_ID CD_START_TIME P_START_TIME NORMAL_DATE_DIFF ADJUSTED_DATE_DIFF
----------- ---------------- ---------------- ---------------- ------------------
1 00:00:00.00000 19:00:00.00000 1140 1140
2 19:00:00.00000 00:00:00.00000 -1140 300
3 06:00:00.00000 00:00:00.00000 -360 1080
4 03:00:00.00000 00:00:00.00000 -180 1260
5 23:00:00.00000 00:00:00.00000 -1380 60
August 31, 2016 at 6:56 am
+1 Eirikur
I wouldn't have thought that would have worked. From somewhere I had the idea that time datatypes always "assumed" day 0. (Our systems always use datetime where time is significant.) Good to learn something new.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 31, 2016 at 9:43 am
I'd prefer to just add one day to the end date when needed. That seems much "cleaner" than dual 3ms adjustments:
SELECT
start_time, end_time, DATEDIFF(MINUTE, start_time, CASE WHEN start_time > end_time
THEN DATEADD(DAY, 1, end_time) ELSE end_time END) AS minutes_diff
FROM (VALUES('19:00','00:00'),('02:00','13:00'),
('00:00','19:00'),('21:00','01:45')) test_times(start_time, end_time)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply