Update sequence of time where hour, min, AM/PM in separate columns?

Transact-SQL
1

I'm dealing with a 3rd party database to update a series of schedules. To make things simple, my table looks like this.

|Hour | Min |
| 7 | 0 |
| 7 | 0 |
| 7 | 0 |
| 7 | 0 |

There are over 100 rows, I need to update the entire table so that each MIN is incremented by 10 and once that incrementation reaches 50, the HOUR row is incremented by 1.

Example

|Hour | Min |
| 7 | 10 |
| 7 | 20 |
| 7 | 30 |
| 7 | 40 |
| 7 | 50 |
| 8 | 0 |
| 8 | 10 |
| 8 | 20 |
| 8 | 30 |
| 8 | 40 |
| 8 | 50 |
| 9 | 0 |

There is of course and ID column and I have a where portion of the update clause that will be the same. I tried it by this, but this didn't work, I can't seem to break out once it hits 59.

declare @min int
declare @hour int
set @min=0
set @hour=6

if @min = 59
set @hour = @hour + 1

update dbo.Jobs set @min = starttimeminute = @min + 1
update dbo.Jobs set starttimehour = @hour, @min = 0
2

I'm a bit confused, since you said "increment by 10" but then added "1".

But something like this:

UPDATE dbo.Jobs
SET min = CASE WHEN min >= 50 THEN (min + 10) - 60 ELSE min + 10 END,
    hour = CASE WHEN min >= 50 
                THEN CASE WHEN hour = 23 THEN 0 ELSE hour + 1 END
                ELSE hour END
3

Thanks, I just tried that query and it sets every single value of my min= 10, I ran it again and it sets it to 20.

Yes it's an increment by 10. Also it's a 12 hour format, but once the min goes to 50 the hour should increment by 1, while the min goes to 0.

4

Since you've provided no directly useable test data at all, I can't help you further.

5

Well this here is the create table statement.

USE [test]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[ScheduleJobs](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[starttimehour] [int] NULL,
	[starttimeminute] [int] NULL,
	[startTimeAmPm] [varchar](50) NULL,
 CONSTRAINT [PK_ScheduleJobs] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

The data is pretty simple. Fill the starttimehour with 7, fill the startimemintue with 0 and fill the AM/PM portion with AM.

6

Something like this perhaps:

declare @add_min int=10;

update s
   set s.starttimehour=s.starttimehour+floor((s.starttimeminute+t.mins)/60)
      ,s.starttimeminute=(s.starttimeminute+t.mins)%60
      ,s.starttimeampm=convert(varchar(7)
                              ,dateadd(hour
                                      ,s.starttimehour+floor((s.starttimeminute+t.mins)/60)
                                      ,dateadd(minute
                                              ,(s.starttimeminute+t.mins)%60
                                              ,cast('00:00' as time)
                                              )
                                      )
                              ,0
                              )
  from dbo.schedulejobs as s
       inner join (select ss.id
                         ,(row_number() over(order by ss.id)-1)*@add_min as mins
                     from dbo.schedulejobs as ss
                  ) as t
               on t.id=s.id
;

Or if you want to be able to subtract aswell:

declare @add_min int=-7;

update s
   set s.starttimehour=datepart(hour
                               ,dateadd(hour
                                       ,s.starttimehour+floor((s.starttimeminute+t.mins)/60)
                                       ,dateadd(minute
                                               ,(s.starttimeminute+t.mins)%60
                                               ,cast('00:00' as time)
                                               )
                                       )
                               )
      ,s.starttimeminute=datepart(minute
                                 ,dateadd(hour
                                         ,s.starttimehour+floor((s.starttimeminute+t.mins)/60)
                                         ,dateadd(minute
                                                 ,(s.starttimeminute+t.mins)%60
                                                 ,cast('00:00' as time)
                                                 )
                                         )
                                 )
      ,s.starttimeampm=convert(varchar(7)
                              ,dateadd(hour
                                      ,s.starttimehour+floor((s.starttimeminute+t.mins)/60)
                                      ,dateadd(minute
                                              ,(s.starttimeminute+t.mins)%60
                                              ,cast('00:00' as time)
                                              )
                                      )
                              ,0
                              )
  from dbo.schedulejobs as s
       inner join (select ss.id
                         ,(row_number() over(order by ss.id)-1)*@add_min as mins
                     from dbo.schedulejobs as ss
                  ) as t
               on t.id=s.id
;
8

Hi I have got the below .. Please see if it works for you

SET statistics time, io ON; 

go 

-- dropping temp tables  
IF Object_id('tempdb.dbo.#ScheduleJobs', 'U') IS NOT NULL 
  DROP TABLE #schedulejobs 

IF Object_id('tempdb.dbo.#tally1', 'U') IS NOT NULL 
  DROP TABLE #tally1 

go 

-- creating tables 
CREATE TABLE #schedulejobs 
  ( 
     id                INT IDENTITY (1, 1), 
     [starttimehour]   [INT] NULL, 
     [starttimeminute] [INT] NULL, 
     CONSTRAINT [PK_ScheduleJobs] PRIMARY KEY CLUSTERED ( [id] ASC )WITH ( 
     pad_index = OFF, statistics_norecompute = OFF, ignore_dup_key = OFF, 
     allow_row_locks = on, allow_page_locks = on) ON [PRIMARY] 
  ) 
ON [PRIMARY] 

CREATE TABLE #tally1 
  ( 
     rownum1 INT NULL, 
     min1    INT NULL 
  ) 

go 

-- Populate tables with data  
DECLARE @ID INT = 1 

WHILE @ID < 1001 
  BEGIN 
      INSERT INTO #schedulejobs 
                  (starttimehour, 
                   starttimeminute) 
      VALUES      (7, 
                   0) 

      SET @ID = @ID + 1 
  END 

go 

INSERT INTO #tally1 
VALUES      (1, 
             0), 
            (2, 
             10), 
            (3, 
             30), 
            (4, 
             40), 
            (5, 
             50), 
            (6, 
             59) 

go 

-- Final Select to give Result             
SELECT a.starttimehour, 
       a.starttimeminute, 
       ( a.starttimehour + a.batchno ) % 24, 
       b.min1, 
       CASE 
         WHEN ( ( a.starttimehour + a.batchno ) % 24 ) BETWEEN 0 AND 12 THEN 
         'AM' 
         ELSE 'PM' 
       END AS AMPM 
FROM   (SELECT id, 
               1 + ( ( Row_number() 
                         OVER ( 
                           ORDER BY id) - 1 ) % 7 )                        AS 
               rownum, 
               Floor(Cast(Row_number() 
                            OVER ( 
                              ORDER BY (SELECT 1)) AS DECIMAL(10, 2)) / 7) AS 
               BatchNo, 
               Floor(Cast(Row_number() 
                            OVER ( 
                              ORDER BY (SELECT 1)) AS DECIMAL(10, 2)) / 7) AS 
               BatchNo1, 
               starttimehour, 
               starttimeminute 
        FROM   #schedulejobs) a 
       JOIN #tally1 b 
         ON a.rownum = b.rownum1 

go 

SET statistics time, io OFF;

9

Will this work for you?

Declare @ScheduleJobs As Table (
        ID int Identity(1, 1) Primary Key 
      , StartTimeHour int Not Null
      , StartTimeMinute int Not Null
      , StartTimeAMPM char(2) Not Null 
        );

 Insert Into @ScheduleJobs (StartTimeHour, StartTimeMinute, StartTimeAMPM)
 Select StartTimeHour = 7
      , StartTimeMinute = 0
      , 'AM'
   From dbo.Tally t
  Where t.N <= 100;

   With calc (NewDateTime)
     As (
 Select dateadd(hour, s.StartTimeHour, dateadd(minute, 10 * (row_number() over(Order By (Select 1)) - 1), 0))
   From @ScheduleJobs s
        )
 Select *
      , NewStartTimeHour = datepart(hour, c.NewDateTime)
      , NewStartTimeMinute = datepart(minute, c.NewDateTime)
      , NewStartTimeAMPM = format(c.NewDateTime, 'tt')
   From calc c;