asked on

comparing a datetime in WHERE clause inMFC CRecordset

I'm using MFC CRecordset class to select records from an ODBC Data Source (Access). I'm trying to set a filter for a datetime column (named "Date") compared to a value I'm building from data in my program.

for values of m_strFilter, I've tried tried:
"Date > '03/30/00 10:00:00'"
"Date > '10:00:00 03/30/00'"

... and just about every variation on the date format. No records are selected (triple checked that they should have been). I also tried leaving out the time, and comparing against the date only.

I saw some articles referencing the CONVERT function, so I tried that ("Date > CONVERT(datetime,'03/30/00')") However, that gives a CDatabaseException with reason: "CONVERT is an unknown function"

Guy Hengel [angelIII / a3]

Try this:
"[Date] > #2000/03/30 10:00:00#"
or
"[Date] > #2000/03/30#"

sniles

ASKER

I tried those, but they did not work. With no single quotes around the date field (as in your suggestion), nothing was selected. When no single quotes were used a record from 3/28/00 and the record from 3/30/00 was selected.

paasky

Hello sniles,

Use CDate() with Access database:

Date > CDate('03/30/00 10:00:00')
Date > CDate('10:00:00 03/30/00')

It converts value to valid date.

Hope this helps,
Paasky

sniles

ASKER

Thanks! That did work. Is this is a general solution? You specifically mentioned Access -- will this also work with SQL Server (& other ODBC)?

ASKER CERTIFIED SOLUTION

paasky

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sniles

ASKER

Thanks again!