SQL query between date range

[SimonChin]

Hi All,

Would like to get the date range with SQL, but it seem give the incorrect date range.

Possible can advise with the attach code & database.mdb

Thanks,

Simon

[aputman]

I can't open your VI so I'll just make some assumptions for now.

select * from table where datestamp  >= '2023-01-01' and datestamp <= '2023-01-31'

If you input your datestamps in this format, the first date is inclusive and the second date is exclusive, because the dates essentially have a timestamp of 00:00:00.  So your query will not return any data from 1/31/2023 unless the timestamp on the data is exactly '00:00:00'. 

So you could do something like the following:

select * from table where datestamp  >= '2023-01-01 00:00:00' and datestamp <= '2023-01-31 23:59:59' (explicit timestamps

- or -

select * from table where datestamp  >= '2023-01-01' and datestamp < '2023-02-01' (implied timestamps)

[Martin_Henz]

I could verify that the results are incorrect.

If I change the first date to several other values it returns correct results.

correct results for:

BETWEEN #5/05/2023# AND #20/05/2023#

BETWEEN #13/5/2023# AND #20/5/2023#

and all other days larger or equal to 13

seems like a bug in the MS SQL parser.

Looks like another reason for my recommendation: don't use ms access for databases.

[SimonChin]

Hi Aputman,

It give an error as below if using '. If using #, there is no error but return wrong data.

ADO Error: 0x80040E07
Exception occured in Microsoft JET Database Engine: Data type mismatch in criteria expression. in NI_Database_API.lvlib:Conn Execute.vi->Testing_DateBetween.vi

Attach code in jpg format and the date format in database using short date.

Thanks,

Simon

[SimonChin]

Hi Martin,

There's another solution is to convert the timestamp to LabVIEW time in string and save to database.

When search for time range, fetch all data and convert back to integer and do the comparison.

It might out of memory one day.

Regards,

Simon

[aputman]

Have you tried using mm/dd/yyyy format? I know there are regional display options within Windows where the default format can be altered. However, I'm not sure how this affects Access or Labview. Based on Martins response, it appears that the date is being interpreted correctly when it's obvious which value is the month (when one value is >12).  So it would seem there is a conflict between regional settings and either Labview or Access ODBC driver.  

[tst]

Because there are different formats for date and time, it's best to use a standardized format which will be supported by the DBMS. For Jet, Microsoft says you should use the escape-clause syntax, so for you example it would be something like WHERE DateCal >= {d '2023-05-10'}

[SimonChin · Accepted solution]

Hi,

Thank you All for the respond.

Is working and verify with below statement:

1. SELECT Sales_Order FROM Jobs_Info WHERE DateCal BETWEEN #2023-05-26# AND #2023-05-27#

2. SELECT Sales_Order FROM Jobs_Info WHERE DateCal BETWEEN #2023/05/26# AND #2023/05/27#

3. SELECT Sales_Order FROM Jobs_Info WHERE DateCal >= #2023/05/26#

Not working statement:

1. SELECT Sales_Order FROM Jobs_Info WHERE DateCal BETWEEN {d'2023-05-26'} AND {d'2023-05-27'}

2. SELECT Sales_Order FROM Jobs_Info WHERE DateCal BETWEEN #26/05/2023# AND #27/05/2023#

So the date format statement must be correct (YYYY/MM/DD) else data return incorrect and only accept #.

No change in access database format for date (DD/MM/YYYY).

Thanks,

Simon