Query over two columns

Question

Query over two columns

macca18

SSC Enthusiast

Points: 124
More actions
May 11, 2006 at 7:38 am

#366111

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

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

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

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

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]

Luke L SSC-Dedicated Points: 35547 More actions · Answer 2

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]

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 3

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)