Hi,
I use ms sql.I have a field called "date_created" which has the value "getdate()" by default.
and it returns the full date like this:3/7/2006 1:31:53 AM

I want to filter the records which are added during the past 1 hour
how should I change the sql statement
I have the following line but it returns the records from yesterday up to now.

RsHistory.open "SELECT *,(SELECT COUNT(*) FROM ecard_history WHERE ecard_history.date_created between " & "'" & date()-1 & "'" & " AND " & "'" & date() & "'" & " ) AS Rec_COUNT from ecard_history",Conn,3,3

Try using DATEPART. You can then perform filtering based on specific parts of the date and time (month, day, hour, minute). It would look something like this (untested):

RsHistory.open "SELECT *,(SELECT COUNT(*) FROM ecard_history WHERE ecard_history.date_created>=DATEPART(hour,GETDATE( ))-1 AS Rec_COUNT from ecard_history",Conn,3,3

It does not work!
PLEASE give a sample

Looks like you're asking to return all the records (SELECT *) and a count of the records (SELECT COUNT(*)), but you had them nested incorrectly. I tested this in Query Analyzer and it returned the data I expected from a test database. Try this:

RsHistory.open "SELECT * FROM ecard_history WHERE ecard_history.date_created>=(DATEPART(hour,GETDATE ())-1)(SELECT COUNT(*) AS Rec_COUNT FROM ecard_history)",Conn,3,3