Jump to content

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

Link to comment

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

Link to comment

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.

Link to comment

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.