May 11, 2006 at 7:38 am
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
May 11, 2006 at 8:20 am
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.
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?
May 11, 2006 at 8:37 am
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
May 11, 2006 at 9:19 pm
Absolutely!
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply