Date/Time erros when working with Database Connectivity toolkit

[Gurdas]

Dear Xu,

1) Is yyyy-mm-dd the ISO datetime format?

2) You mean to say formatting my string as yyyy-mm-dd will work irrespective of the datetime format setting in SQL? If yes, how is that possible?

3) We also use some search queries that locate data between "start" and "end" dates. These are the dates we store in SQL. Until now, we have been saving the date as mm-dd-yyyy and use the same format when building our SQL query. Now, if I start saving the dates in yyyy-mm-dd format, will I also need to make changes in my SQL query?

Thanks,

Gurdas

[Xu]

Hi Gurdas,

1. yyyy-mm-dd is the ISO date format, so is yyyymmdd.
2. Actually, yyyymmdd is independent on the SQL settings. yyyy-mm-dd format is dependent on the SQL settings. If people set dateformat to ydm (I think there is very little chance), yyyy-mm-dd will confuse SQL Server. My previous post was based on my memory, I mixed yyyy-mm-dd and yyyymmdd somehow.
3. If you use yyyymmdd format and keep the current setting not changed, you do not need to change your queries. But if the current setting changes, I think you may have to change your queries.

Cheers,

Xu

[Necesito Conectar DataLogin con SQL]

Hi, everyone... I need your help, i need convert to string datetime to stamp datetime, for transfer to DB SQL server.

please it´s importante. thanks

[Gurdas]

Hi,

I am not sure if I have understood you correctly but if you are looking for a vi to convert your datetime string into a time stamp (and into number of seconds) then the attached vi (pls unzip) should help.

Best regds,

Gurdas

[Dynamik]

In the unlikely-case that someone might actually be able to use this VI, here it is again, with the following correction:

changed "Offset" from 1461.67 to 1461.666666666666740 (the number of days between 12AM Dec 30, 1899 and 4PM Dec 31, 1903)

I found this "feature" a few days after first posting the VI, but withheld the correction thinking that, perhaps, it was a bad idea to start with (writing dates directly as DBLs) and that the post should die a natural death.

But the thread was referenced recently and I still think it's simplest to avoid Date/Time string-handling-headaches by writing DBLs directly.  A trade-off is that while one can write to an MSAccess Date/Time type as if it were a DBL, querying the field returns a Date/Time string.  This can be a real pain if the LabVIEW code needs to see a DBL!  So my database stores the MSAccess time-stamp as a Numeric (not Date/Time.)

Message Edited by Dynamik on 12-10-2005 11:47 PM

[JCFC]

(Three years later )
I recently found your VI and use it ... but now the offset is 1461.791666666666740 (LV starts now in 12:00 a.m., Friday, January 1, 1904)
It seems that it's working fine with Access

[rolfk]

---

@JCFC wrote:
(Three years later )
I recently found your VI and use it ... but now the offset is 1461.791666666666740 (LV starts now in 12:00 a.m., Friday, January 1, 1904)
It seems that it's working fine with Access

---

LabVIEW timestamp "seconds since" always started at 12:00 AM, January 1, 1904 UTC. Plese note the UTC! The shown date time is however by default in local time, and before 8.0 you had no direct choice to display UTC at all.

As to inserting timestamps into MS SQL Server using CONVERT(DATETIME, 'yyyy-mm-dd hh:mm:ss.uuu', 121) should work fine independant of local settings, but it is likely not portable to other SQL server applications.

Rolf Kalbermatter

[tbd]

Hi JCFC,

      Glad somebody (else) found this util useful :^) - and thanks for the pointing-out the inaccuracy/bug!

The extra-8 hours "error" is related to a normal/expected UTC-8 "correction" automatically applied by LabVIEW.  Rolfk figured it out in this thread where I posted a tool for obtaining DaylightSavingsTime-independent timestamps (thanks again, RolfK!).  I think sometime after 7.1, LabVIEW became able to build timestamps w/out DST compensation, so that tool is becomming obsolete.  However,...

I've been using the technique of writing times directly to DB as DBL for three years now, and highly recommend it for avoiding time-string/numeric conversions.  Even if somebody-elses code is doing the conversions, the inefficiency and potential for bugs "makes my skin crawl".  Three months ago we changed the time-columns of our DB from DBL to datetime-type - it was completely painless, all the timedata was retained and stayed accurate (because datetime types are really DBLs by another name.)  BTW, SQL 2000 uses the same fundamental datetime value - days since Jan 01, 1900.

Thanks for the feedback w/bug fix.

Cheers!

Message Edited by tbd on 01-13-2008 11:26 PM

[tbd]

... sorry, forgot to mention the I used to be Dynamik.

[tst]

Ah, so that's where Dynamik went.

Too bad, I liked Dynamik...

BTW, if you had only renamed the user, you would have had more than 1000 posts today.