SQL Toolkit -- problem searching Date/Time field in Access

[Eldon_Skousen]

I have got an application where I need to query a database (Access) between
two dates (usually the same date, a couple of hours apart). I have set up
the date field within Access as a 'General Date' but can't seem to get the
right format on the SQL query. I am always getting some error. I enclose
the date with {}, '', [], and () (and anything else I can think of) and I
still get a problem. It is now to the point where I think it might be a
problem with SQL Toolkit and Access working together.
I tried making the Date field into a string field (instead of a Date field),
but I can't search between time (I can search between dates, but not date
and time i.e. MM/DD/YY 00:00:00).
Any ideas?

[jjgors]

Try putting the date into the format #mm-dd-yy#. The "#" signs should be included.

[obuchwen]

I have had exactly the same issue.  I want to do an sql query.

between two date times ie.  perhaps the same date, but two different times.

I have tried using the following:  SELECT * FROM ValveMeritData WHERE MeritTestDateTime >= 2006-06-06 and

SELECT * FROM ValveMeritData WHERE MeritTestDateTime >= 2006\06\06.

The database format for datetime is 2006\06\06 13:45:32

[obuchwen]

neither of the above queries worked and so I

tried using the # signs as suggested above.  This didn't work either.

Any other possibilities?

Anyone tried this?SELECT * FROM ValveMeritData WHERE MeritTestDateTime >= 2006-06-06

[Dennis_Knutson]

Try enclosing your date/time in single tick marks like '2006-06-06' or '2006\06\06'. You don't mention the database that you are using but you should try all of your queries there before writing the LabVIEW code.

[obuchwen]

Sorry, the database type is Access.

I just tried the following query SELECT * FROM ValveMeritData WHERE MeritTestDateTime >= '2006/05/01 13:24:28'

and SELECT * FROM ValveMeritData WHERE MeritTestDateTime >= '2006/05/01'  (which gave a data mismatch error).

I am going to try the query in access now.  I had pretty good success with other types of queries, just writing them in

Labview and testing.

Any other suggestions?

[obuchwen]

Hello again.

Have any of you done these types of queries with Access successfully?  It would be helpful to know that

the correct syntax does exist if I can just find it..

cheers,

[obuchwen]

Hello again.  I have tested a query written in Access, and in Access it works and selects the appropriate records.

If i go to the Access SQL view, the WHERE portion of the query is shown below.

SELECT * FROM ValveMeritData WHERE (((ValveMeritData.MeritTestDateTime)>#5/2/2006 11:0:10#))

However, it returns no records when run in labview.  Can this be explained?

[mbabik]

I use this ...WHERE (Some_date_column <= #2011-10-07 23:59:59#).

[mbabik]

I use this ...WHERE (Some_date_column <= #2011-10-07 23:59:59#).