DB Connectivity Toolkit Date Format

[prettypwnie]

I'm using a MySQL database and it has some columns of the datatype DATETIME. When I store data in these fields, I format my timestamps into a string of the YYYY-MM-DD HH:MM:SS format and it works just fine. However, I'm having a problem when I get the DATETIME values back out of the database. I'm using the DB Tools Select Data VI and then Database Variant to Data to convert it into a string (it won't let me convert directly to a timestamp). The problem is that the string I get is the date and time, formatted according to my system date format, which for me is MM/DD/YYYY II:MM:SS p. I want it to stay in the YYYY-MM-DD HH:MM:SS format, so I can use that format to convert it back to a timestamp. I've tried changing my system date format, and that fixes the problem, but I'm afraid it's not a very robust solution. I don't want my program to break if I try to run it on someone's computer that has a different system date format. Does anyone have any ideas of how I can make it always output the same format?

Thanks!

--Hope

[LogMAN]

I don't have experience with MySQL specifically, but it should work similarly to MSSQL on this behalf. Check these two sources:

http://www.w3schools.com/sql/func_date_format.asp

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

Your query should be something like this:

SELECT DATE_FORMAT(column, "%Y-%m-%d %H:%i:%s") FROM table_name;

Use the Scan From String function to convert the string into a LabVIEW time stamp:

[prettypwnie]

Thanks so much for your response! I just tried what you said of typing in a query with the DATE_FORMAT to the database in workbench and it works great, it returns the date in whatever format I specify. What I'm confused about, though, is how do I get that from LabVIEW? The DB Tools Select Data VI doesn't have a date format input or anything where I think I could put that kind of parameter.

[LogMAN]

You can execute any SQL query using the Execute Query VI from the Advanced palette. Here is an example I just put together (untested of course):

[prettypwnie]

Oh, this is great, it works perfectly! I did see the Execute Query VI there on the advanced palette, but I couldn't figure out how to use it because I didn't know what to do with the recordset reference, so this is exactly the example I needed. Thanks so much!