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

[b.headley]

Hello,

  I have a similiar issue with LabVIEW 8.6 and the Database Connectivity Toolkit.  I'm performing a SQL query (Complete SQL Session.vi):

  SELECT Ser_Num,Date_Test WHERE Date_Test BETWEEN '11/01/2011' AND '11/30/2011' ORDER BY Date_Test;

  The array returned contains data from this and prior years (11/01/2011, 11/01/2010, 11/01/2009, etc).  The Date_Test field is a text field in the table, the text is formatted with leading zeros (01/01/2011 e.g.) so the width remains constant.

  Any suggestions?

[Dennis_Knutson]

Don't use a text field. You must define the column as date/time. Thats basic database design. You are getting a search based on the ASCII value.

[b.headley]

I tried changing the table column to a Date/Time format in Access, but when I tried it no records were returned.  I tried various date/time formats with no results.

I also created a test Access table from scratch (just to make sure there wasn't embedded junk) and still no success.

The text search works with the exception that all years are returned, like it completely ignores the year value.

[PhillipBrooks]

---

@b.headley wrote:

Hello,

  I have a similiar issue with LabVIEW 8.6 and the Database Connectivity Toolkit.  I'm performing a SQL query (Complete SQL Session.vi):

  SELECT Ser_Num,Date_Test WHERE Date_Test BETWEEN '11/01/2011' AND '11/30/2011' ORDER BY Date_Test;

  The array returned contains data from this and prior years (11/01/2011, 11/01/2010, 11/01/2009, etc).  The Date_Test field is a text field in the table, the text is formatted with leading zeros (01/01/2011 e.g.) so the width remains constant.

  Any suggestions?

---

Are you delimiting the field in your SQL with a # as shown in other posts?

  SELECT Ser_Num,Date_Test WHERE Date_Test BETWEEN #11/01/2011# AND #11/30/2011# ORDER BY Date_Test;

http://stackoverflow.com/questions/4183180/simple-sql-microsoft-access-date-question

[b.headley]

I tried BETWEEN '#09/28/2011#' AND '#12/09/2011#' - no records were returned.  I've also tried the % delimiter with the same results.

I also tried BETWEEN #09/28/2011# AND #12/09/2011#, this returned a syntax error.

[PhillipBrooks]

Try WHERE Date_Test >= #11/1/2011# AND Date_Test <= #11/30/2011#

[b.headley]

Hey Philip,

  I tried the basic formats Date_Test >= 12/01/2011 AND Date_Test <= 12/31/2011, but it still returns records for current and prior years.  It's really strange that it will return the correct month and date ranges, but it seems to ignore the years.

  Another test that somewhat worked was Date_Test BETWEEN '12/01/2011' AND '12/31/2011' AND Date_Test LIKE '%2011%', but the problem is the query typically spans 90 days and can overlap from one year to the next.  I tried the Date_Test >= '%2011%' but it returns no records.

  I can get the data ranges wanted if I use the LIKE statement in a incrementing date loop, but it takes a long time to execute (about 90 iterations).

  If someone has an example using the DB Toolkit instead of the SQL Toolkit, I'd be willing to give it a try.

[PhillipBrooks]

Which toolkit are you using? The NI LabVIEW DB Connectivity Toolkit, or something else?

[b.headley]

Sorry Phillip, I was a little vague.

  LabVIEW 8.6 with the LabVIEW Database Connectivity Toolkit 1.0.2. Normally we use the SQL vi's but also have the DB vi's that come with the toolkit.

  The database is basically Microsoft Access flat tables, posted on a SQL server.  All columns of the tables are text type.  I have tried changing between text and date/time types on the date column, but could not get any results.