LV reads date from SQL server in a different format

[Dave76]

HI,

I am reading data from an SQL server using Labview, one of the fields is a datetime field. The data that was written to the table was is the format of 'YYYY-MM-DD HH:MM:SS.SSS"  i am reading it back using FetchRecordsetData.vi i think this is part of the database connectity toolkit.  This vi returns the data in an array but the datetime field changes to "DD/MM/YYY HH:MM:SS"

am i missing something obvious here? has anyone else seen this?

i can post some code if my description is not claer enough.

[aputman]

It probably has to do with your region settings on your PC.  In Control Panel, click Region and Language (Windows 7).  If you don't want to change your region settings, you should be able to cast the datetime field to a string field in your query. 

[aputman]

Actually, instead of casting the datestamp, you'll want to CONVERT it and specify a style.  121 seems to be the style you'll want. 

[mikeporter]

To tell the truth, date and time data is something that no DBMS handles really well. However it has gotten better. While they will output datetime data in a certain format, it will accept input in a variety of others automatically.

In terms of solutions for your problem, I typically see what format the database returns by default and then write a routine to convert that string into a LabVIEW timestamp value. One thing that makes this approach practical is that over time more and more systems have started using an ISO standard time representation.

Mike...

[Dave76]

Thanks for your replies i found a different solution to my problem due to some details i omited in the original post, i didn't originally say that this was a problem becasue my code may be used all over the world. 

i didn't realise that no mater what format you save the data in or read the  data in, it will display it as the default format set on your system.  let me explain this further, 

if i save a date of '21/07/2016 13:00:12' to SQL it stores it '2016-07-21 13:00:12.000' and vice verse if i read the date from SQL it will read it as '21/07/2016 13:00:12'.  the reson for this is the date format set up on your PC.  So if i change the format to 'mm/dd/yy' the date i save is '07/21/16 13:00:12' SQL will still save this as '2016-07-21 13:00:12.000' and  this would casue me complications in my code so the solution i used was to read the registry of the PC to find out what the short date format of the date is and then have a different case for each format while wiring the correct wires to the day, month and year to be used however needed later on.  maybe the image will show this more clearly.

I hope this makes sence its a little tricky to explain easily

 

[aputman]

Why would you do it that way?  What happens if you miss a format?  That is a lot of unnecessary case structures.  If you use the method I posted above, the date will be returned in the same format, no matter the settings of the computer.  That way you only have one case and you can extract the month, day and year from it as required.  Let the SQL engine do the work for you.