February 14, 2017 at 6:15 am
Hello,
This actually may be more of a math problem than anything else.... apologies if inappropriate.
We have an application that uses two fields as input and a third field takes those two and calculates (or is supposed to calculate) the time difference. The issue is that we are trying to calculate the amount of hours and minutes (or even just getting to 2.5 for two and a half hours) between the two fields, but the fields are varchar and don't support time. Let's say you want to know how long you're sleeping for if you input 11PM as the start time and 7AM as the end time. These values would be entered in as 1100 and 0700 (as in, one thousand one hundred and seven hundred). How does one go about calculating this?
And yes, we've gone to the app development manager about it and he basically told us to shove it because he didn't think of that 🙂 any help would be appreciated.
Thanks!
February 14, 2017 at 6:22 am
scarr030 - Tuesday, February 14, 2017 6:15 AMHello,This actually may be more of a math problem than anything else.... apologies if inappropriate.
We have an application that uses two fields as input and a third field takes those two and calculates (or is supposed to calculate) the time difference. The issue is that we are trying to calculate the amount of hours and minutes (or even just getting to 2.5 for two and a half hours) between the two fields, but the fields are varchar and don't support time. Let's say you want to know how long you're sleeping for if you input 11PM as the start time and 7AM as the end time. These values would be entered in as 1100 and 0700 (as in, one thousand one hundred and seven hundred). How does one go about calculating this?
And yes, we've gone to the app development manager about it and he basically told us to shove it because he didn't think of that 🙂 any help would be appreciated.
Thanks!
How do you tell the difference between am and pm in your example?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 14, 2017 at 6:39 am
If the values are 24 hour (contrary to your post) and the difference no longer than 24 hours thenDECLARE @Time1 varchar(10) = '1100', @Time2 varchar(10) = '0700';
DECLARE @Minutes int = DATEDIFF(minute,
CAST(STUFF(@Time1,3,0,':')+':00' as datetime),
CAST(STUFF(@Time2,3,0,':')+':00' as datetime));
IF @Minutes < 0 SET @Minutes += 1440;
SELECT CONVERT(char(5),DATEADD(minute,@Minutes,0),108);
Far away is close at hand in the images of elsewhere.
Anon.
February 14, 2017 at 6:41 am
That's part of the issue. The users seem to be using the 12 hour clock, but I think if we can get them to use the 24 hour clock (2300 instead of 11PM), it could make it easier
February 14, 2017 at 6:45 am
scarr030 - Tuesday, February 14, 2017 6:41 AMThat's part of the issue. The users seem to be using the 12 hour clock, but I think if we can get them to use the 24 hour clock (2300 instead of 11PM), it could make it easier
It's probably impossible unless you use the 24 hour clock. Here's something to play with - try it with different values.
Then take your lead developer to a well-known hamburger joint and get them a new job in something better suited to their skill set.
SELECT HoursDifference = DATEDIFF(MINUTE,StartTime,EndTime)/60.0
FROM (
SELECT
StartTime,
EndTime = CASE WHEN EndTime < StartTime THEN DATEADD(DAY,1,EndTime) ELSE EndTime END
FROM (SELECT StartTimeString = '1100', EndTimeString = '0700') d
CROSS APPLY (
SELECT
StartTime = CONVERT(DATETIME,STUFF(d.StartTimeString,3,0,':'),108),
EndTime = CONVERT(DATETIME,STUFF(d.EndTimeString,3,0,':'),108)
) x
) e
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 14, 2017 at 6:53 am
Even if you add 24 hour clocks, you're still going to have problems if you have something last longer than a day, as you're recording no details of the start end date. If this is something that could or does happen, then you're going to need a redevelopment on your current set up.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 14, 2017 at 7:55 am
ChrisM@Work - Tuesday, February 14, 2017 6:45 AMscarr030 - Tuesday, February 14, 2017 6:41 AMThat's part of the issue. The users seem to be using the 12 hour clock, but I think if we can get them to use the 24 hour clock (2300 instead of 11PM), it could make it easierIt's probably impossible unless you use the 24 hour clock. Here's something to play with - try it with different values.
Then take your lead developer to a well-known hamburger joint and get them a new job in something better suited to their skill set.SELECT HoursDifference = DATEDIFF(MINUTE,StartTime,EndTime)/60.0
FROM (
SELECT
StartTime,
EndTime = CASE WHEN EndTime < StartTime THEN DATEADD(DAY,1,EndTime) ELSE EndTime END
FROM (SELECT StartTimeString = '1100', EndTimeString = '0700') d
CROSS APPLY (
SELECT
StartTime = CONVERT(DATETIME,STUFF(d.StartTimeString,3,0,':'),108),
EndTime = CONVERT(DATETIME,STUFF(d.EndTimeString,3,0,':'),108)
) x
) e
It looks like this could work! Just need to work on some data type issues to get it to mesh with the app. Thanks!
Thom A - Tuesday, February 14, 2017 6:53 AMEven if you add 24 hour clocks, you're still going to have problems if you have something last longer than a day, as you're recording no details of the start end date. If this is something that could or does happen, then you're going to need a redevelopment on your current set up.
Well, I don't think that would happen because this is recording sleep times. I can't imagine people regularly sleep more than 24 hours! Otherwise, good point.
February 14, 2017 at 8:05 am
scarr030 - Tuesday, February 14, 2017 7:55 AMThom A - Tuesday, February 14, 2017 6:53 AMEven if you add 24 hour clocks, you're still going to have problems if you have something last longer than a day, as you're recording no details of the start end date. If this is something that could or does happen, then you're going to need a redevelopment on your current set up.
Well, I don't think that would happen because this is recording sleep times. I can't imagine people regularly sleep more than 24 hours! Otherwise, good point.
[/quote]
You obviously haven't met my "better" half! 😉
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 14, 2017 at 10:12 am
I don't think AM/PM is an issue really. Here's the code to calc the diff in minutes; I'll leave converting those minutes to hours:mins or hours.hours as I'm pressed for time and that part's trivial anyway :-).
SELECT start_time, end_time, DATEDIFF(MINUTE, start_time4, end_time4) AS minutes_diff
FROM (
VALUES('11PM','7AM'),('2230','630AM'),('11PM','9'),('8','6')
) AS test_data(start_time, end_time)
CROSS APPLY (
SELECT CASE WHEN start_time LIKE '%PM%' THEN 12 ELSE 0 END AS start_pm_flag,
CASE WHEN end_time LIKE '%PM%' THEN 12 END AS end_pm_flag
) AS ca1
CROSS APPLY (
SELECT CASE WHEN start_time LIKE '%[AP]M%' THEN STUFF(start_time, PATINDEX('%[AP]%', start_time), 2, '')
ELSE start_time END AS start_time2,
CASE WHEN end_time LIKE '%[AP]M%' THEN STUFF(end_time, PATINDEX('%[AP]%', end_time), 2, '')
ELSE end_time END AS end_time2
) AS ca2
CROSS APPLY (
SELECT RIGHT('000' + CASE WHEN start_time2 LIKE '%[^0-9]%' THEN '0'
WHEN start_time2 < 24 THEN CAST(start_time2 + start_pm_flag AS varchar(2)) + '00' ELSE start_time2 END, 4) AS start_time3,
RIGHT('000' + CASE WHEN end_time2 LIKE '%[^0-9]%' THEN '0'
WHEN end_time2 < 24 THEN end_time2 + '00' ELSE end_time2 END, 4) AS end_time3
) AS ca3
CROSS APPLY (
SELECT CAST(STUFF(start_time3, 3, 0, ':') AS smalldatetime) AS start_time4,
DATEADD(DAY, CASE WHEN start_time3 > end_time3 THEN 1 ELSE 0 END, CAST(STUFF(end_time3, 3, 0, ':') AS smalldatetime)) AS end_time4
) AS ca4
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply