Filtering DateTime Column

[nazeem_khan]

How do i filter a recordset on DateTime Column for a given date(sql Server)?...

[andreys]

Format it first to "mm/dd/yyyy" then filter.

[nazeem_khan]

Originally posted by andreys
Format it first to "mm/dd/yyyy" then filter.

well....

I tried converting the DateTime Column to dd/mm/yyyy column but it didnt work. Anyway i will explain again. I already have a recordset, which have one or more datetime columns. I need to compare only the given date with the datetime column. Because it is datetime column, when i compare it is not giving the result properly. So i need to convert the datetime column to date column for filtering records based on a given date.

I tried giving like this but it didnt work in filtering....
Format([Membership Expiry Date],'dd/mm/yyyy') = 06/04/2003

[swatty]

rst.Filter "[Membership Expiry Date] = " & "2004/04/06"

[nazeem_khan]

Originally posted by swatty

rst.Filter "[Membership Expiry Date] = " & "2004/04/06"

I mean the Filter Column is DateTime Column And Filter Value is Date Only. Database i'm using is SQL Server 2000...Can u plz give me solution for this....

[swatty]

When filtering on a datetime field with only a date you need to get the values between date 00:00:00 And date 23:59:59 +1

Dim d As Date
Dim dd As Date
d = Date
dd = DateAdd("d", 1, d)
rst.Filter "[Membership Expiry Date] Between #" & d & "# And #" & dd & "#"

If you got a date value , add a day to it these are used like
2003/04/06 00:00:00
2003/04/07 00:00:00

[nazeem_khan]

Originally posted by swatty
When filtering on a datetime field with only a date you need to get the values between date 00:00:00 And date 23:59:59 +1

Dim d As Date
Dim dd As Date
d = Date
dd = DateAdd("d", 1, d)
rst.Filter "[Membership Expiry Date] Between #" & d & "# And #" & dd & "#"

If you got a date value , add a day to it these are used like
2003/04/06 00:00:00
2003/04/07 00:00:00

Let me try and let u know....thanx

[nazeem_khan]

Originally posted by nazeem_khan
Let me try and let u know....thanx

I tried ur code but instead of # if i give it in single quotes it works. But i have one more question?. How do i pick only the dates that not between 08/02/2003 12:00:00AM and 08/02/2003 11:59:59PM. Thanx for ur help in the previous post and i would really appreciate if u could help me in this...