>hi all,
>a friend asked me to have a look at some code over the weekend as it has him, and me now, stumped. there is a field called trandate that is of type datetime and in the query he selects out from an inputted field but this field is of type date not datatime. he selects out for a week, say the 5th-11th, but in the report the data for the 11th is missing. in SQL if he uses the query on the table the same thing happens, its only when the date is changes to 5-12 that the 11th details come in. the query is select * from table1 where trandate [gt. eq. to] startdate and [lt. eq. to] enddate.
>Slán
>~M

If you don't specify the time portion of a datetime field, SQL Server assumes 12:00 Midnight. So an expression that is lt. eq. to 9/12/2005 will only pick up rows that are lt. eq. to 9/12/2005 12:00 AM (the moment that 9/11/2005 becomes 9/12/2005).

The inputted field must use a different rule for converting a date to a datetime. The best way to fix the problem is to explicitly drop the time portion of the inputted field in the WHERE clause. E.g.:

-- Receives @FromDate And @ToDate as datetime parameters

set @FromDate = CONVERT(datetime, CONVERT(char(8), @FromDate, 112), 112)
set @ToDate = CONVERT(datetime, CONVERT(char(8), @ToDate, 112), 112)

SELECT
    m.MyDate
FROM
    dbo.MyTable m
WHERE
    CONVERT(datetime, CONVERT(char(8), m.MyDate, 112), 112) BETWEEN @FromDate AND @ToDate