March 10, 2015 at 7:54 am
Hello, I am looking to be able to add 15 minutes to a time value that is in character format. here is sample data:
0530
0545
0600
0615
0630
0645
0700
0715
0730
0745
Whenever there is a leading zero, I need to preserve that as well. Here is an example of what I an looking for:
0545 + 15 = 0600
0600 + 15 = 0615
1345 + 15 = 1400
Thank you!
March 10, 2015 at 8:11 am
DECLARE @TimeChar CHAR(4) = '0545';
SELECT REPLACE(LEFT(CONVERT(VARCHAR(20),DATEADD(MINUTE,15,CONVERT(TIME,LEFT(@TimeChar,2) + ':' + RIGHT(@TimeChar,2) + ':' + '00')),114),5),':','')
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 10, 2015 at 8:12 am
this is a great example of using the wrong datatype. you have to jump through extra hoops to get data done right.
you'll have to convert to time or datetime, add the minutes, then convert it back to varchar, i guess.
what would you expect to happen if you added 15 minutes to 23:45? do you expect 00:00 or a duration like 1:00:00?
;WITH MyCTE([stringTime])
AS
(
SELECT '0530' UNION ALL
SELECT '0545' UNION ALL
SELECT '0600' UNION ALL
SELECT '0615' UNION ALL
SELECT '0630' UNION ALL
SELECT '0645' UNION ALL
SELECT '0700' UNION ALL
SELECT '0715' UNION ALL
SELECT '0730' UNION ALL
SELECT '0745'
)
SELECT
stringTime,
stuff([stringTime],3,0,':'),
convert(time,stuff([stringTime],3,0,':')),
dateadd(minute,15,convert(time,stuff([stringTime],3,0,':')))
FROM MyCTE;
Lowell
March 10, 2015 at 8:20 am
DECLARE @I VARCHAR(10)
SET @I = '0745'
IF( SUBSTRING(@I,1,1)= 0)
SET @I = '0'+ CAST((CAST(@I AS INT)+15) AS VARCHAR(10))
ELSE
SET @I = CAST((CAST(@I AS INT)+15) AS VARCHAR(10))
IF( SUBSTRING(@I,1,1)= 0)
SELECT CASE WHEN SUBSTRING(@I,3,2)='60' THEN '0'+ CAST((CAST(@I AS INT)+40) AS VARCHAR(10)) ELSE @I END
ELSE
SELECT CASE WHEN SUBSTRING(@I,3,2)='60' THEN CAST((CAST(@I AS INT)+40) AS VARCHAR(10)) ELSE @I END
March 10, 2015 at 8:21 am
Thank you for these great solutions.
David
March 10, 2015 at 8:25 am
sunder.bugatha (3/10/2015)
DECLARE @I VARCHAR(10)SET @I = '0745'
IF( SUBSTRING(@I,1,1)= 0)
SET @I = '0'+ CAST((CAST(@I AS INT)+15) AS VARCHAR(10))
ELSE
SET @I = CAST((CAST(@I AS INT)+15) AS VARCHAR(10))
IF( SUBSTRING(@I,1,1)= 0)
SELECT CASE WHEN SUBSTRING(@I,3,2)='60' THEN '0'+ CAST((CAST(@I AS INT)+40) AS VARCHAR(10)) ELSE @I END
ELSE
SELECT CASE WHEN SUBSTRING(@I,3,2)='60' THEN CAST((CAST(@I AS INT)+40) AS VARCHAR(10)) ELSE @I END
This fails if you use '0750' as your start time.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 13, 2015 at 2:13 pm
CELKO (3/12/2015)
CREATE TABLE Timeslots(slot_start_time TIME(1) NOT NULL PRIMARY KEY,
slot_end_time TIME(1) NOT NULL,
CHECK (start_time < end_time));
INSERT INTO Timeslots --15 min intervals
VALUES ('00:00:00.0', '00:14:59.9'),
('00:15:00.0', '00:29:59.9'),
('00:30:00.0', '00:44:59.9'),
('00:45:00.0', '01:00:59.9'),
..
('23:45:00.0', '23:59:59.9');
Gosh NO! You're missing an entire 10th of a second and if someone uses the table for a different resolution that you built the table for, they're in for quite the failure! Stop thinking of using BETWEEN for temporal comparisons. Use the "closed" (inclusive) and "open" (exclusive) method of SomeTime >= slot_start_time and < next_start_time.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 16, 2015 at 3:29 pm
SELECT
time_char,
STUFF(CONVERT(varchar(5), DATEADD(MINUTE, 15, STUFF(time_char, 3, 0, ':')), 8), 3, 1, '')
FROM (
SELECT '0545' AS time_char UNION ALL
SELECT '0600' UNION ALL
SELECT '1345' UNION ALL
SELECT '2355'
) AS test_data
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".
March 17, 2015 at 5:17 pm
CELKO (3/17/2015)
Gosh NO! You're missing an entire 10th of a second and if someone uses the table for a different resolution that you built the table for, they're in for quite the failure! Stop thinking of using BETWEEN for temporal comparisons. Use the "closed" (inclusive) and "open" (exclusive) method of SomeTime >= slot_start_time and < next_start_time.
That is actually called the “half-open interval” model and it is the basis for the ISO model and almost all the temporal database projects. The minority position is “Chronons”, which see time as a sequence of distinct points of time. Chris Date used this in his book (http://www.amazon.com/Time-Relational-Theory-Second-Edition/dp/0128006315).
Part of my goal in setting up a single granularity was to avoid having to deal with different resolutions. This is like the classic machine shop problem where the mixed tolerances make it hard to put together a working machine. One story is the Rolls Royce automatic transmission. RR bought a design from Ford, then made it to RR precision. It was so well-made that gears squeezed out the transmission fluid and ti would not work; they had to make it looser. Same thing happens with handguns sometimes.
It was not a problem when all we had was DATETIME and not DATETIME2(n). Well actually the floating point rounding in the 3-digit decimal fraction was messy. How much code was wasted trimming things to "00:00:00.00" to get a date? :crazy:
For commercial stuff, a minute is probably fine but the seconds are easy to catch with DATETIME2(0). Nobody tracks billable hours to the 1/10 of a second. In fairness, Telecom apps might need nanoseconds but this is rare.
The advantage of being able to use the BETWEEN predicate are much clearer code. We are trying to express one predicate over a continuous variable and not two different AND-ed predicates. And some SQLs like DB2 have optimizations for BETWEEN that use some of the ranges in an index.
Heh... I can't really tell if you're in support of "Half open" spans or if you're supporting BETWEEN (fully closed spans) but if the experts are recommending using BETWEEN for date spans, then to hell with the "experts" and the "best practices" cited by such experts because it's wrong even by your own words. Having an "end date" that you use BETWEEN on isn't a "Half open" span. It's a fully closed span and certainly violates the intent of ISO.
As for "no body tracks billable hours to a 1/10 of a second", that's absolutely true but I pity the poor fool that finds and uses such code when something else is needed or if the data-type changes behind the scenes because of "new requirements". To be sure, the biggest problem occurs when people attempt to calculate the end date. For example, have you ever seen someone calculate the end date by subtracting 1ms from a date? Depending on the datatype used, that will round up to the next day and can have disastrous results. Or, better yet, someone uses a time in their end date (typically, 23:59:59:999) that's either inappropriate for the current datatype or the datatype that someone changes the column to in the future. And, to be honest, I don't give two hoots how DB2 handles this stuff because this isn't a DB2 forum. I am sure, though, that DB2 will correctly respond to the "Half open" date span notation.
"Half open" spans are the way to go. BETWEEN is NOT the way to do "half open" spans nor is it the right way to go even in the face of supposedly "guaranteed" whole dates because things can and do change. If you do it the right way all the time, then you never have to worry about doing it the wrong way. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 21, 2015 at 2:04 pm
CELKO (3/20/2015)
If you like math, here is a link to “interval algebra” and other options for the math:
Good golly! No wonder many programmers hate temporal math! Allen even repeats himself between Table 1, Figure 4, and Table 7. For most of what we need, all of that can be boiled down to the simple predicate of AStart<=BEnd AND AEnd>=BStart (when both intervals are fully closed). And, correct me if I'm wrong, but that cannot be accomplished with BETWEEN. It could only be accomplished with BETWEEN when BStart=BEnd and was expressed as a single point rather than an interval pair. That will also handle the "cusp" that you speak of even if "A" or "B" are half-open on the end-side.
Shifting gears, I still can't tell if you're pro or con in the use of BETWEEN for determining if two data ranges overlap. I can see it if "B" is a single point in time and "A" is a guaranteed fully-closed interval but that does rely on the data being correct for the situation. The only trust I put in such a thing is that the situation will change so I'm opposed to attempting to form the data rather dealing with it in super simple, bullet-proof code. That also means that I'm seriously opposed to the use of BETWEEN for any temporal criteria even with supposed guarantees that the resolution of the data won't change because I've seen such guarantees change in the past. My problem with BETWEEN is because it represents a fully-closed interval.
So, which is it? Are you opposed to the use of BETWEEN or are you in favor of using it over all other methods? For me, the answer is NOT "It Depends". Because of past experience with changes, I don't use BETWEEN for temporal criteria anymore.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply