Roland February 2016

SQL Query on a MS Access CSV Export using PHP involving dates

February came and broke my sql. I get a generated CSV file that is basically a dump from an MS Access database. I have no ability to modify the database. I use PHP to run queries on the CSV files in order to generate reports for the boss. Here are the queries that I have tried to run (number 1 worked until February came):

1) SELECT * FROM [Orders.csv] WHERE [Order Taken By Associate Surrogate #] = 1009 AND [Date/Time Taken] BETWEEN #05/01/2016# AND #05/07/2016# AND NOT [Order Status Surrogate #] = 3

2) SELECT * FROM [Orders.csv] WHERE [Order Taken By Associate Surrogate #] = 1009 AND Format([Date/Time Taken], 'MM/dd/yyyy') BETWEEN #05/01/2016# AND #05/07/2016# AND NOT [Order Status Surrogate #] = 3

3) SELECT * FROM [Orders.csv] WHERE [Order Taken By Associate Surrogate #] = 1009 AND Format([Date/Time Taken], 'mm/dd/yyyy hh:nn:ss') BETWEEN #05/01/2016 00:00:00# AND #05/07/2016 23:59:59# AND NOT [Order Status Surrogate #] = 3

When I run query 1 I get no results; while 2 & 3 return everything. If I run query 2 for a single day then it returns the correct results. There are 71 records for the time period in queries.

What would be the correct way to query the dates and have the proper results displayed?

Last bit of info:

'Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq='.$file.';Extensions=asc,csv,tab,txt;Extended Properties="Excel 8.0;IMEX=1"'

Answers


HansUp February 2016

You reported the driver sees your [Date/Time Taken] field as Date/Time datatype.

So you should not use the Format() function when comparing it to Date/Time values. Format() returns a string, not a Date/Time value.

Access will interpret #05/01/2016# to mean May 1st. If you actually intend it to mean Jan 5th, use #2016-01-05# to avoid confusion about which piece is the month and which is the day.

With both those suggestions, use a condition like this in your WHERE clauses ...

[Date/Time Taken] BETWEEN #2016-01-05# AND #2016-07-05#

Or if I guessed wrong about the dates ...

[Date/Time Taken] BETWEEN #2016-05-01# AND #2016-05-07#

Post Status

Asked in February 2016
Viewed 3,446 times
Voted 8
Answered 1 times

Search




Leave an answer