Jump to content
prettypwnie

DB Connectivity Toolkit Date Format

Recommended Posts

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

Share this post


Link to post
Share on other sites

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:

String To Timestamp.png

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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):

Execute SQL Query.png

  • Like 1

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.