Adding Offset to Time Interval

I have two columns Interval and Offset in a table TimeZone and the data looks like:

Interval             Offset
730                  60
830                  60
1000                 60

开发者_C百科I am trying to get a third column from these too which will give me like:

Interval             Offset        Interval_Offset
730                  60            0830 
830                  60            0930
1000                 60            1100

select CONVERT(INT, interval / 100) + OFFSET / 60 
from Timezone

I am getting only hour but not minute.

Can anyone help me?

declare @T table (interval varchar(4), offset int)

insert @T values
('730', 60),
('830', 60),
('1000', 60);

select
  interval,
  offset,
  replace(convert(char(5), dateadd(mi, convert(int, interval) % 100 + offset, dateadd(hour, convert(int, interval) / 100, 0)), 8),':','')
from @T

You have to convert first to hours, then minutes in order to do hour arithmetic accurately, like this:

with q1 as (
  select 730 interval, 60 offset
  union all
  select 830, 60
  union all
  select 1000, 60
  union all 
  select 1145, 30
  union all
  select 1130, 90
  ),
  q2 as (
  select interval, offset
         ,  interval / 100 int_hour
         , (interval % 100) + offset int_minute
    from q1
  )  
select interval, offset
       , (int_hour + int_minute / 60) * 100 + (int_minute % 60) result_hour
  from q2    

Result is:

interval    offset      result_hour
730         60          830
830         60          930
1000        60          1100
1145        30          1215
1130        90          1300

I added a couple of values to show you it operates well on different intervals. Of course this is still limited, if it works for you depend on what we don't know about the problem you're facing..

Date and time math is notoriously difficult. I suggest you use the proper datatypes (i.e. since you're on SQL 2008, use a time type for your Interval column) and then use dateadd to add the offset to it.

Some maths..

create table TimeZone (interval int, offset int)
insert TimeZone values
(730, 60),
(830, 60),
(1000, 60);

select
    interval, offset,
    ((interval+offset+40)/100)*100 + ((interval+OFFSET)%100)%60 Interval_Offset
from Timezone

Output

interval    offset      Interval_Offset
----------- ----------- ---------------
730         60          830
830         60          930
1000        60          1100

On the other hand, if interval is a datetime column, you can use

create table TimeZone (interval datetime, offset int)
insert TimeZone values ('7:30', 60), ('8:30', 60), ('10:00', 60);

select
    interval,
    convert(char(5),interval,8) Interval_display,
    offset,
    dateadd(mi, offset, interval) Interval_Offset,
    convert(char(5),dateadd(mi, offset, interval),8) Interval_Offset_display
from Timezone

Output (the 2nd and 5th columns format the time for display in HH:MM)

interval                Interval_display offset      Interval_Offset         Interval_Offset_display
----------------------- ---------------- ----------- ----------------------- -----------------------
1900-01-01 07:30:00.000 07:30            60          1900-01-01 08:30:00.000 08:30
1900-01-01 08:30:00.000 08:30            60          1900-01-01 09:30:00.000 09:30
1900-01-01 10:00:00.000 10:00            60          1900-01-01 11:00:00.000 11:00

Since Interval is a time type (according to your comment), you can use DATEADD():

SELECT DATEADD(m, Offset, Interval)
FROM Timezone