Hi, Mark.

>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 >= startdate and <= enddate.

This is due to the fact that SQL Server only has a datetime, not date type, so when you say:
<= '20050912'
you are actually saying
<= '20050912 00:00'
which discard anything before the first minute of today.

Making it:
< '20050913'
(that is adding a day) will include the ending date completely.

Regards,