>>Possible. I met datetime2 on a remote machine only, where I didn't have a choice. It's still quite a rare beast out in the field - among few dozen databases from others that I saw in the last few years, there was only this one case. And even then it served no purpose - it contained just dates, no times. Whoever used it didn't quite RTFM.
>
>Dragan,
>
>I've never had any trouble with datetime2 and it definitely supports a time component in the data.
Like I said, I saw it used only once and I had to hop through four machines to get there (rdp to our server, then into a VM which was allowed to connect to their connection server then rdp to their actual server), with just enough rights to get the conversion done. Have a hand in their choice of ODBC driver? Forget it. Use what you have, which is most probably the old SQL 2000 driver, because that one still comes with every flavor of Windowses. For the rest you need to rely on the client's IT dept... Again, forget it, not worth the hassle and mostly never gets done.
>One of the best things is that the range of Datetime2 is 0001-01-01 through 9999-12-31.
>For the old datetime datatype the range is only January 1, 1753, through December 31, 9999 - the Gregorian calendar.
>
>Sure, 1753 is probably enough of a starting range, but I can't tell you how many crashes I've had to investigate because the user supplied bad date data in a string that is prior to 1753. You get a SQL error. I'd prefer that the bad date data gets into the db and I'll deal with it later versus an app crash regardless of your error handling or lack thereof.
Ah, don't get me started. Of course I had the same problem when importing data from various sources - among tens of thousands of records you can be sure there will be at least a couple of percent of records with weird dates - year 206 instead of 2006, or 3006 for that matter, or 1294 instead of 1924 etc etc. And it could at least leave the oddball dates blank or null, but no, integrity above anything else, it breaks.
Worse than that - I have a c(10) field which generally contains digits only, because it's coming from a counter. But that field is also a fk into a related table, pointing to an integer key. Among the 20000 records in that particular db (and I've been through dozens of instances of it, each client has at least one), there was, I guess, a glitch once and there's a duplicate key. Whoever tried to fix that without really deleting the record has just added an "x" to the end of the number, so when you do a join it bangs with a "cannot convert a char field into an integer'. It absolutely never ever confesses as to which fields were involved or what was the PK of the row. So hunting that one took hours.
>I really don't want to be boastful, but when I worked at MS in the FoxPro group as a contractor one of my areas was testing FoxPro and ODBC to MSSQL and Oracle. In fact, when I was given that task the server that the SQL Server (version 4.?) was on had 0 bytes of free space on the C drive because some idiot was storing video movies on the server. I changed all that. And for each of my tests I would restore a fresh version of the old Pubs database so that the tests always had clean data to work with.
>
>Finally, one of my greatest disappointments is that FoxPro's datetime datatype does not include fractional seconds the way SQL Server does. Instead FoxPro just supports whole number seconds without a fractional part. Yet, the FoxPro datetime datatype is 8 bytes, which is more than enough to store fractional seconds. Heck, this new MSSQL Datetime2 is 8 bytes or less. I actually discussed this with Calvin H. and he said that it could be easily done. I put in an ER, but they never did anything about it. I suspect it would have required a new datatype with a name of its own. Remember that FoxPro dates are stored on disk as simple text, but datetime is binary data on disk. And an extended FoxPro datetime would be binary as well. Again, it would all fit easily into 8 bytes.
But it did. Around the time of, IIRC, VFP5, the datetime did store milliseconds. (The upper integer of the datetime holds the julian date number; the lower contains milliseconds since midnight. That's in the specs.) However, for display purposes, they were omitted, and then you had the problem with comparing values. At some point it was decided that while a field may hold milliseconds, a variable should not, so if you did a scatter or any other assignment to a variable or property, it would lose them. Then you'd use that in a select or a for clause, and there was a 99,9% chance that the record from which you took the value wouldn't fit the "equals" or "not greater than" criteria. I've checked that several times back then - browse fiel dt, diff=dt-ctot(ttoc(dt)), and guess what - there were very few records where the diff column contained zeros. Of course, the chances of it being zero are exactly one permill.
