Showing results for 
Search instead for 
Did you mean: 

MySQL timestamp formatting errors

Go to solution

I'm continuing to have odd formatting errors when pulling data out of a MySQL database.  The one I'm currently having is with time values. 


MySQL Variant to Data Error.jpg



The screenshot shows that the error is being generated in the Variant to  The actual time stored in the database is 8:30AM  But when converted to timestamp, I get 8:29:59.999  Unfortunately, there doesn't seem to be an easy way to 'round' this value back to what I want either.  


I could go back to string outputs, since that particular error doesn't seem to propagate into a string.  But that messes with the rest of my application.  Going back to string output undoes a couple of days work, and leaves me with the problem of having to convert string 'time' back into timestamp that LabVIEW can understand.


The really odd thing is that 12:30 comes out as 12:30 just fine.  I'm not sure why this would be.  Any ideas?  


Patrick Allen:
0 Kudos
Message 1 of 6
You have 3 digits on your input to the variant.  What happens if you change the display format of you constant to 0 digits?
0 Kudos
Message 2 of 6

There's no difference at all.  Changing the properties of the constant doesn't seem to effect the output at all. 


I'm trying to figure a way to convert the string back into a timestamp now via these instructions. (example at the bottom of the page)  


But my string out put is in the form 7/7/2009 8:30:00 AM  and there doesn't seem to be a way at all to use the scan from string tool to covert that back into a time stamp.  


Even if I manipulate my string output to be exactly 8:30:00 AM 7/7/2009, the example and any variation I can think of simply won't work.  I always get the error that the data is not in the expected format.   

Message Edited by pallen on 07-07-2009 12:05 PM
Patrick Allen:
0 Kudos
Message 3 of 6
Accepted by pallen

You can do it with the format string "%<%m/%d/%Y %I:%M:%S %p>T"


Timestamp conversion.png

Message 4 of 6

Thanks a lot. 


I don't have a lot of experience using these formating strings, and I just couldn't seem to get it quite right.  What you posted converts my string output back into a timestamp just perfectly.   :smileyvery-happy:


I'm still curious though why it gets messed up in the first place.  This is going to be a workaround for me.  But it is going to cost me some time to implement it throughout my code.


Patrick Allen:
0 Kudos
Message 5 of 6

I have run into the same problems, and I think it is bit errors in the conversion, similar to, if not exactly like, what happens when you try to represent a number as a float.  Sometimes you cannot exactly represent the number, and I think that is what is going on here.  I ended up doing the string conversion, because, like you, the conversion error caused problems.  For me, I was using the date as a lookup reference elsewhere in the database, so when the date got corrupted, it never matched anything in the database.


Another option you could try is to convert the dates to a DBL, use round to nearest, and convert them back to timestamps.

0 Kudos
Message 6 of 6