Filtering DateTime Column

Printable View

Apr 8th, 2003, 12:50 PM
nazeem_khan

Filtering DateTime Column

How do i filter a recordset on DateTime Column for a given date(sql Server)?...:confused: :confused: :confused: :confused:
Apr 8th, 2003, 05:05 PM
andreys

Format it first to "mm/dd/yyyy" then filter.
Apr 9th, 2003, 02:13 AM
nazeem_khan

Quote:

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

VB Code:

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

Apr 9th, 2003, 03:39 AM
nazeem_khan
Quote:
Originally posted by swatty

VB Code:

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....:eek: :eek: :eek: :eek:
Apr 9th, 2003, 03:51 AM
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
VB Code:
```
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
Apr 9th, 2003, 03:59 AM
nazeem_khan
Quote:
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

VB Code:

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 :) :) :) :)
Apr 9th, 2003, 09:51 AM
nazeem_khan

Quote:

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...:) :) :) :)