use 'between' with varchar (sql server)

Question

Using SQL Server 2005 Express.

(
 CONVERT(VARCHAR(8), R.reviewStart, 108) between CONVERT(VARCHAR(8), M.meetingStart, 108) and CONVERT(VARCHAR(8), M.meetingEnd, 108) OR
 CONVERT(VARCHAR(8), R.reviewEnd, 108) between CONVERT(VARCHAR(8), M.meetingStart, 108) and CONVERT(VARCHAR(8), M.meetingEnd, 108) OR
 CONVERT(VARCHAR(8), M.meetingStart, 108) between CONVERT(VARCHAR(8), R.reviewStart, 108) and CONVERT(VARCHAR(8), R.reviewEnd, 108) OR
 CONVERT(VARCHAR(8), M.meetingEnd, 108) between CONVERT(VARCHAR(8), R.reviewStart, 108) and CONVERT(VARCHAR(8), R.reviewEnd, 108)
)

Will the "between" still have the expected behavior after the datetimes have been converted to varchar?

Thanks

Answer 1

Yes, depending on what you mean by expected behavior. The BETWEEN operator will treat these operands as varchars, and apply its comparison rules accordingly:

BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.

Now, I can see a lot of potential problems, comparing strings and expecting date comparison behavior. I haven't seen any in my tests, but look carefully at your data. Is the CONVERT returning 24-hour time, with the appropriate leading zeroes?

This question has some other approaches to comparing dateless-times, other than converting them to varchars.

Also, watch for null dates, which will cause the corresponding WHERE condition to return false (actually, unknown).

In your other question, you indicated that you were getting an error. If so, can you post that?

Answer 2

Your first condition is equivalent to this more index friendly one:

    R.reviewStart >=  DATEADD(day, DATEDIFF(day, '19010101', M.meetingStart), '19010101')

and R.reviewStart < DATEADD(day, 1+DATEDIFF(day, '19010101', M.meetingStart), '19010101')

(explained here: http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/05/23/reuse-your-code-with-cross-apply.aspx ) .

Answer 3

As I said in your other post if is possible you should consider moving to SQL 2008 because of the new datetime types that allow explicit separation of date part and time part so your filter expressions become much simpler and you can index by time. Since its Express there really shouldn't be any reason to hold you back at 2005.