>We have a number of queries we write that look only at the date portion of a datetime field. I was wondering if anyone has an efficient way of looking at just the date. For example
>
>SELECT * from MyTable WHERE date = '01/01/2007'
>
>With the above query the following would be selected
>
>DATE = 01-01-2007 00:00:00
>
>And the following would not:
>
>DATE = 01-01-2007 01:30:50
>
>or,
>
>SELECT * from MyTable WHERE date BETWEEN '01/01/2007' AND '01/02/2007'
>
>With the above query the following would be selected
>
>DATE = 01-02-2007 00:00:00
>
>And the following would not:
>
>DATE = 01-02-2007 01:30:50
>
>
>Thanks.
>
>Bob Bartel
Not pretty, but here:
SELECT *
FROM MyTable
WHERE DueDate = CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, '1/1/2007')))
I read there was function in 2005 to specifically handle this, but this is how I do it in 2000.