locked
comparing time range for reservation system query RRS feed

  • Question

  • Hi,

    im having problem with my query on trying to compare time.

    This is the query,

    select * from tbl_library_sched where (schedule_date=@schedule_date AND user_code=@user_code AND ((CONVERT(VARCHAR,'2:00:00 PM',108) >= CONVERT(VARCHAR,time1,108))) AND  ((CONVERT(VARCHAR,'2:00:00 PM',108) <= CONVERT(VARCHAR,time2,108))))

    time1 = 1:30:00 PM
    time2 = 3:45:00 PM

    what i am trying to do is to compare the time to reserve for a hotel if the current time range has not been taken.

    Please help
    Sunday, February 7, 2010 9:15 AM

Answers

  • I don't get it, it doesn't work in my query, could it have been because sql server translate 12 hours format into 24 hour format?


    select * from tbl_library_sched 
             where schedule_date=@schedule_date AND user_code=@user_code AND DATEPART(HOUR,time1)<=DATEPART(hour,'02 PM') AND   DATEPART(HOUR,time2)>=DATEPART(hour,'02 PM')
    


    Chase Excellence - Success Will Follow!
    Sunday, February 7, 2010 11:50 AM
  • you should pass both starttime and endtime for the reservation..
    suppose, some one ask to reserve a room between 1:00 and 2:00 PM you should pass these both times ...

    assume these are @starttime and @endtime

    then the below query will returns the no of records that are in between the time... so, if the count > 0, then that means already the time is alloted..
    and also my suggestion is to convert time1 and time2 columns to datetime columns instead of varchar columns.... 

    select * from tbl_library_sched where (schedule_date=@schedule_date AND user_code=@user_code  )
    and
    (
      ( time1 >= @starttime and time1 <=@endtime ) or
      (time2 >= @startime and time2 <= @endtime ) or 
      (time1 > @starttime and time2 < @endtime)
    )


    Sunday, February 7, 2010 6:19 PM

All replies

  • select * from tbl_library_sched
    where schedule_date=@schedule_date AND user_code=@user_code AND DATEPART(HOUR,time1)<=2  AND   DATEPART(HOUR,time2)>=2
    Chase Excellence - Success Will Follow!
    Sunday, February 7, 2010 9:46 AM
  • the query should return a result if the person reserved under the range time1 to time2 since 2 PM is what the person requested.

    Sunday, February 7, 2010 10:20 AM
  • the query should return a result if the person reserved under the range time1 to time2 since 2 PM is what the person requested.


    if exists(select * from tbl_library_sched
    where schedule_date=@schedule_date AND user_code=@user_code AND DATEPART(HOUR,time1)<=2  AND   DATEPART(HOUR,time2)>=2)

    begin
    print 'Yes,you can reseve!'
    declare @status varchar(100)
    set @status= 'Yes,you can reseve!'
    --you can return this variable
    end
    Chase Excellence - Success Will Follow!
    Sunday, February 7, 2010 10:23 AM
  • I don't get it, it doesn't work in my query, could it have been because sql server translate 12 hours format into 24 hour format?

    Sunday, February 7, 2010 10:42 AM
  • I don't get it, it doesn't work in my query, could it have been because sql server translate 12 hours format into 24 hour format?


    select * from tbl_library_sched 
             where schedule_date=@schedule_date AND user_code=@user_code AND DATEPART(HOUR,time1)<=DATEPART(hour,'02 PM') AND   DATEPART(HOUR,time2)>=DATEPART(hour,'02 PM')
    


    Chase Excellence - Success Will Follow!
    Sunday, February 7, 2010 11:50 AM
  • you should pass both starttime and endtime for the reservation..
    suppose, some one ask to reserve a room between 1:00 and 2:00 PM you should pass these both times ...

    assume these are @starttime and @endtime

    then the below query will returns the no of records that are in between the time... so, if the count > 0, then that means already the time is alloted..
    and also my suggestion is to convert time1 and time2 columns to datetime columns instead of varchar columns.... 

    select * from tbl_library_sched where (schedule_date=@schedule_date AND user_code=@user_code  )
    and
    (
      ( time1 >= @starttime and time1 <=@endtime ) or
      (time2 >= @startime and time2 <= @endtime ) or 
      (time1 > @starttime and time2 < @endtime)
    )


    Sunday, February 7, 2010 6:19 PM
  • You may want to check the following link that talks about date range queries

    http://wiki.lessthandot.com/index.php/Date_Range_WHERE_Clause_Simplification
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Sunday, February 7, 2010 6:24 PM