How do I Format DateTime Data in WHERE Clause of SELECT Statement Using Execute Query.vi?

[evaneer]

When I execute this statement in MS Access, it is a valid statement and my data is returned from my MySQL Database

SELECT a.TestDate, a.UUTModel, a.UUTGeneratedSN, a.TestID, a.FTResult
FROM TestDatabase a
WHERE(a.TestDate BETWEEN #04/10/2019 12:00:00 AM# AND #04/17/2019 11:59:00 PM#)

But when I run this in LabVIEW using Execute Query.vi, it complains about the # signs around the DATETIME values. How can I format this BETWEEN statement so that LabVIEW correctly queries the DATETIME data type?

MySQL Table Properties

LabVIEW Error

[cstorey]

I've never used # and its been awhile since I had to use MS Access, but it looks like you need []..

SELECT a.TestDate, a.UUTModel, a.UUTGeneratedSN, a.TestID, a.FTResult 
FROM TestDatabase a 
WHERE(a.TestDate BETWEEN [04/10/2019 12:00:00 AM] AND [04/17/2019 11:59:00 PM])

OR 

SELECT a.TestDate, a.UUTModel, a.UUTGeneratedSN, a.TestID, a.FTResult 
FROM TestDatabase a 
WHERE(a.TestDate >= [04/10/2019 12:00:00 AM] AND a.TestDate <= [04/17/2019 11:59:00 PM])

MS Access isn't standar SQL syntax, I don't think the BETWEEN function works so second method is likely better.  You should also check MS Access for the date time format expected - https://support.office.com/en-us/article/examples-of-using-dates-as-criteria-in-access-queries-aea83...

[evaneer · Accepted solution]

Thank you for the fast reply. The brackets didn't work but your formatting suggestion gave me an idea to check the DATETIME format syntax in MySQL.

https://dev.mysql.com/doc/refman/8.0/en/datetime.html

Apparently even though the DATETIME visually looks like

04/10/2019 12:00:00 AM

The format actually looks like

2019-04-10 00:00:00

So I adjusted my query to look like this:

SELECT a.TestDate, a.UUTModel, a.UUTGeneratedSN, a.TestID, a.FTResult
FROM TestDatabase a
WHERE(a.TestDate BETWEEN '2019-04-10 00:00:00' AND '2019-04-17 23:59:00')

This worked and returned all of my desired data. So for all future readers of this, if querying a DATETIME column from a MySQL database in LabVIEW, the syntax is 'YYYY-MM-DD HH:MM: SS' (ignore the last space between : and S, rich text formatting kept changing this into a :S emoji)

[RavensFan]

---

@evaneer wrote:

(ignore the last space between : and S, rich text formatting kept changing this into a :S emoji)

---

Go to your forum profile's preferences and turn off the automatic emoji's, then you won;t see that problem.  Profile Name >> My Settings >> Preferences >> Display >> Emoticon type ->  None.

If everyone turned off automatic emoji's, or better yet, NI just disabled them on the forums, then no one would complain about these things again.