Query over two columns

  • Hi,

    I have a table that includes two DateTime columns. The first column holds the date. e.g 2006/5/10 and the second column holds time e,g 08:15:25.

    I am having a problems specifing a query that will list the rows in the table  between a date time range.

    i.e return all rows between 2006/5/8 09:00:00 and 2006/5/10 21:30:00

    Can anyone give me any suggesstions on how to do this?

    Thanks

    Macca

  • you could combine the columns like so... and use this in your where statement...  If your date column was called datecreated, and your time column was called timecreated.  I'm assuming both columns are datetimes as you said and that the time column looks like this 1900-01-01 08:15:25 (that's what happens when I insert convert(datetime,'08:15:25') into a datetime column.

    datecreated + timecreated

    So you could use...

    Select *

    FROM mytable

    WHERE datecreated + timecreated between 2006/5/8 09:00:00

                                                               and 2006/5/10 21:30:00

    The biggest drawback here is that you will have trouble making use of indices and such.  Any reason why you can't store it in one column? 

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • another option could be to create a computed column and place an index on the computed column to help with performance issues...

     

    Here's a link on how to create a computed column.  then you index it like you would any other column.

    http://www.samspublishing.com/library/content.asp?b=STY_Sql_Server_7&seqNum=101&rl=1

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • Absolutely!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

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