Hi,
I use ms sql db and the date field type is datetime like this:
7/15/2006 10:16:28 PM
The following query does not respond when I want to filter the records for today
I think I should use datepart function before date_signed but I do not know how??

select * from cmm_Users where date_signed=" & "'" & date() & "'" ,Conn,1,3

One way to do this is to use the between keyword:
select * from cmm_Users where date_signed between ...
All of todays dates will be between date() and date() plus one day.

Woody Z http://www.learntoprogramnow.com

No.The datetime format is liek this 7/15/2006 10:16:28 in db
but date() function returns this 7/15/2006
and therefore it does not return the correct value

Please note I am in Aussie the date values shown here are dd/mm/yyyy

There are several ways to do this. My method of getting all records for a certain day. EG today, I would:

sql = "select * FROM cmm_Users WHERE date_signed > '" & date() & "' AND date_signed < '" & (date()+1) & "';"

[give me all records where the dat value is greater than today but less that tommorow]

FYI:
the date() function returns:
21/07/2006
However the now() function returns:
21/07/2006 10:30:25



Wind is your friend
Matt

  You are not understanding.

By using the between keyword, which is inclusive, you will get all the "between" dates - which will be everything that has a date of the current date.

You cannont use "date_signed=" because your dates will (almost) never be equal to the "raw" date without the time portion tacked on.

What you need to do is either use the between, or the
that mat41 supplied.

What we are suggesting is that you need to check for dates that are within a range, and that range must include all the valid datetime entries for the date you are checking.

You see?


Woody Z http://www.learntoprogramnow.com

Thanks a lot
it worked
nice