Post reply

Updating record based on time range.

  • November 30, 2010 at 6:15 am

    #232056

    Hello All,

    Here is my problem:

    create table #temp

    (

    id int,

    hoursFrom int,

    minsFrom int,

    hoursTo int,

    minsTo int

    )

    insert into #temp

    select 1, 12,30,15,45 union all

    select 2, 05,12,16,03 union all

    select 3, 22,50,23,53 union all

    select 4, 12,00,17,24

    select * from #temp

    drop table #temp

    I am updating record with id = 3 and passing hoursFrom,minsFrom,hoursTo and minsTo as 22,58,23,01 respectively. It makes start time 22:58 and end time 23:01. The criteria is: the time must not clash. For example:

    for id = 3 start time is 22:50 and end time is 23:53 so i should not be able to update this record if my start time or end time comes in this range (22:50-23:53). How to achieve this? Thanks.

  • November 30, 2010 at 6:42 am

    #1256749

    Personally I would be looking to use the TIME datatype (or a datetime conversion) like this:

    select CAST(CAST(hoursFrom as varchar(8)) + ':' + CAST(minsFrom as varchar(8)) as TIME)

    from #temp

    From this you can easily do a DATEDIFF and get the differences in MINUTES then you can make sure that you are (or are not) updating records with a MINUTES difference of X

Viewing 2 posts - 1 through 1 (of 1 total)

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