in sql between @startdate and @enddate does not compare time part ? How to compare time parts of date.

Question

I have query like this

SELECT AVAILABILITY_DATE_TIME FROM APPT_PROVIDER_AVAILABILITY
            WHERE AVAILABILITY_DATE_TIME between @START_DATE  AND @END_DATE 

suppose i have if @startdate = '2/11/2010 11:31:00 AM' and @enddate = '2/11/2010 11:56:00 AM'

then difference is zero its ignoring time part .

If you can provide query plz use

table name : APPT_PROVIDER_AVAILABILITY , column name : AVAILABILITY_DATE_TIME

and @start_date and @enddate as params

Thanks in advance

Answer 1

It does not ignore the time portion.

• Check your data- that you really do have a record in that range
• Check your culture settings- that it's not treating the string '2/11/2010' as '11/2/2010' when converting it to a datetime
• Change your parameter assignment. Even if your culture setting is right you should still use an unambiguous value for the assignment. Something more like this: 2010-02-11 11:31:00 AM
• Check your parameter definitions, that you're not using the newer 'date' type.
• If all else fails, write it out in long hand: WHERE ([column] >= @startdate AND [column] <= @enddate)