Professional Web Applications Themes

Comparing Times - Microsoft SQL / MS SQL Server

I'm looking for a way to compare times, more finding the record in a select statetement like this select TimeFRameNumber from aTable where xtime bewteen Start_Of_TimeFrame and Stop_Of_TimeFrame. As this select would go into a function and this function would be called quite a lot i really want to find a way for optimal performance. Kind Regards -- Harry Leboeuf Email Inversed moc.silopenikfueobelh (For Spam-Reason) Visit us at http://www.kinepolis.com...

  1. #1

    Default Comparing Times

    I'm looking for a way to compare times, more finding the record in a select
    statetement like this

    select TimeFRameNumber
    from aTable
    where xtime bewteen Start_Of_TimeFrame and Stop_Of_TimeFrame.

    As this select would go into a function and this function would be called
    quite a lot i really want to find a way for optimal performance.

    Kind Regards

    --
    Harry Leboeuf
    Email Inversed moc.silopenikfueobelh (For Spam-Reason)
    Visit us at http://www.kinepolis.com


    Harry Guest

  2. #2

    Default Re: Comparing Times

    I'm still building the table, i just want to build it so that it can be used
    as fast as possible.

    The goal is to define some time zones ex from 14h30 till 15h15 is zone 1,
    from 15h16 till 17h30 is zone 2, ....

    Then, in a select i want to match a field, that is a datetime field, between
    these time-zones, the result would we a timezone-code.


    "Andrew J. Kelly" <com> wrote in message
    news:%phx.gbl... 
    > select [/ref]
    called 
    >
    >[/ref]


    Harry Guest

  3. #3

    Default Re: Comparing Times

    I would use a datetime for everything and make the Date portions of the
    beginning and end time zones as 19000101. That way you only have to define
    the time zones once (or once per schedule) and not each day. Then when you
    go to do the select create two variables based on the begin and end times
    and use those in the select. Similar to this:

    DECLARE Begin DATETIME, End DATETIME

    SELECT Begin = CONVERT(CHAR(8),GETDATE(),112)) + ' '
    + CONVERT(CHAR(12),YourBeginTime,112)) ,
    End = CONVERT(CHAR(8),GETDATE(),112)) + ' '
    + CONVERT(CHAR(12),YourEndTime,112))
    FROM YourTimeCodeTable WHERE YourTimeCode = x

    SELECT * FROM YourOtherTable WHERE YourSearchTime BETWEEN Begin AND End




    --

    Andrew J. Kelly
    SQL Server MVP


    "Harry Leboeuf" <moc.silopenikfueobelh> wrote in message
    news:%phx.gbl... 
    used 
    between [/ref]
    of 
    > > select [/ref]
    > called 
    > >
    > >[/ref]
    >
    >[/ref]


    Andrew Guest

  4. #4

    Default Re: Comparing Times

    That's a bit how i would have solved it, the other was to store the
    start/stop hour/minute in a smallint and compare them with datepart.

    To be honest, i hoped that there would be a function, like the trunc in
    pl/sql where you can give all possible date-format options in one go. There
    you can compare just hour & min of two dates, or just the minutes & seconds
    or even more crazy combinations.

    Thx for the Help

    "Andrew J. Kelly" <com> wrote in message
    news:%phx.gbl... 
    define 
    you 
    > used [/ref]
    1, 
    > between [/ref][/ref]
    row 
    > > called 
    > >
    > >[/ref]
    >
    >[/ref]


    Harry Guest

  5. #5

    Default Re: Comparing Times

    The advantage of doing it this way vs. using DatePart is that this way can
    use an index on the datetime column where as DatePart will never use an
    index.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Harry Leboeuf" <moc.silopenikfueobelh> wrote in message
    news:%phx.gbl... 
    There 
    seconds 
    > define 
    > you [/ref]
    times [/ref]
    End [/ref][/ref]
    be [/ref]
    > 1, 
    > > between [/ref]
    > row [/ref][/ref]

    > >
    > >[/ref]
    >
    >[/ref]


    Andrew Guest

Similar Threads

  1. comparing two tables
    By Cleverbum in forum MySQL
    Replies: 16
    Last Post: January 20th, 06:16 PM
  2. Comparing Lists
    By 280844 in forum Coldfusion - Advanced Techniques
    Replies: 3
    Last Post: August 22nd, 06:18 PM
  3. Comparing two files
    By BradS in forum Linux / Unix Administration
    Replies: 2
    Last Post: February 1st, 04:57 PM
  4. comparing array value...
    By Ajit P Singh in forum PERL Beginners
    Replies: 3
    Last Post: February 4th, 07:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139