Professional Web Applications Themes

Problem matching datetime. BETWEEN date1 and date2 ? - Microsoft SQL / MS SQL Server

Hi, I want to see wether a given time is between another 2 time parts. In my table I have two fields StartTime and EndTime. I have a stored procedure which accpets a time value which checks wether its between the StartTime and EndTime. I have tried it as below select ..... where inputTime BETWEEN [StartTime] AND [EndTime] it doesn't work. how can I check wether a given date falls in between a range ? Rgds KK...

  1. #1

    Default Problem matching datetime. BETWEEN date1 and date2 ?


    Hi,

    I want to see wether a given time is between
    another 2 time parts. In my table I have two
    fields StartTime and EndTime.

    I have a stored procedure which accpets a
    time value which checks wether its
    between the StartTime and EndTime.

    I have tried it as below

    select ..... where inputTime BETWEEN [StartTime] AND [EndTime]

    it doesn't work. how can I check wether a given
    date falls in between a range ?

    Rgds
    KK


    KK Guest

  2. #2

    Default Re: Problem matching datetime. BETWEEN date1 and date2 ?

    KK wrote: 
    <sigh>
    What are the datatypes of the two columns?


    Bob Guest

  3. #3

    Default Re: Problem matching datetime. BETWEEN date1 and date2 ?

    Did you take into account that a datetime field stores the date and the time
    so you cannot just query date without also a time component?

    "Bob Barrows" <com> wrote in message
    news:%phx.gbl... 
    > <sigh>
    > What are the datatypes of the two columns?
    >
    >[/ref]


    Enzo Guest

  4. #4

    Default Re: Problem matching datetime. BETWEEN date1 and date2 ?

    ?
    I didn't take anything into account. I asked for more information so i
    wouldn't have to make any assumptions.

    Oh, wait ... did you reply to me by mistake? Were you actually intending to
    reply to the original poster?

    Bob

    Enzo Maini wrote: 
    >> <sigh>
    >> What are the datatypes of the two columns?[/ref][/ref]



    Bob Guest

  5. #5

    Default Re: Problem matching datetime. BETWEEN date1 and date2 ?

    Both the fields are datetime type. Now that is the problem
    Seems to me like, if the fields are of datetime type I can't use
    BETWEEN .!! is it?

    So then how to check wether a particular date lies between
    2 given dates?


    "Bob Barrows" <com> wrote in message
    news:phx.gbl... 
    to [/ref]
    >
    >
    >[/ref]


    KK Guest

  6. #6

    Default Re: Problem matching datetime. BETWEEN date1 and date2 ?

    What do you mean by "doesn't work" ? This should
    correct select all rows where inputTime is at least
    [StartTime] and no later than [EndTime].

    Most likely you want to compare the times only or the
    dates only, in which case you need to compare only
    those data, not the entire datetime value. For example,

    cast('12:00:00' as datetime) is not between
    any pair of dates in the year 2003, since cast('12:00:00' as datetime)
    is a datetime value on January 1, 1900.

    If you can give some sample data to show the values of
    StartTime and EndTime in some rows that don't behave
    well, and the value of inputTime you use, it will be easier
    to know what you are looking for.

    -- Steve Kass
    -- Drew University
    -- Ref: 627A0CDF-81AB-40EE-8830-88FD3DB52750

    KK wrote:
     

    Steve Guest

  7. #7

    Default Re: Problem matching datetime. BETWEEN date1 and date2 ?

    This will be easier if you provide some sample data. There are a few
    possibilities:
    1) you have input only times into the StartTime and EndTime columns and you
    are supplying a time for inputTime with the intention of comparing the
    times
    2) you have input both date and time into the StartTime and EndTime columns,
    and you are supplying a date and time for inputTime, with the intention of
    comparing the dates and times
    3) you have input both date and time into the StartTime and EndTime columns,
    and you are supplying a time for inputTime, with the intention of comparing
    only the time portion of the data in the columns to the parameter.
    4) etc.

    Now, I could supply solutions for each of the above possibilities ... but I
    don't like to waste time. So, please provide the sample data (just a few
    rows in tabular form if you like), the value supplied in inputTime, and the
    results you wish the query to return (in tabular form, please - a
    description by itself will not do the job)

    Bob Barrows

    KK wrote: [/ref][/ref]



    Bob Guest

Similar Threads

  1. DateTime Problem with .NET WebService
    By CI2 in forum Macromedia Flash Data Integration
    Replies: 2
    Last Post: March 24th, 11:46 AM
  2. DateTime problem
    By FeyBaby in forum Macromedia ColdFusion
    Replies: 1
    Last Post: May 2nd, 07:29 AM
  3. Database datetime problem
    By Christopher Brandsdal in forum ASP
    Replies: 1
    Last Post: August 1st, 10:37 PM

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