Add 15 minutes to a time in char format

  • 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!

  • 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

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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

  • Thank you for these great solutions.

    David

  • 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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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".

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • CELKO (3/20/2015)


    If you like math, here is a link to “interval algebra” and other options for the math:

    http://www.ics.uci.edu/~alspaugh/cls/shr/allen.html

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply