Does the SQL Server equivalent of the ANSI SQL data type DATETIME (TIMESTAMP, if I'm not mistaken) have the notion of time zone? In a project, I store a complex timestamp, composed of a DATETIME field and a FLOAT type, which gives me a sub-microsecond resolution for thousands of years (all right, it's a bit of an overkill but not by much). I use both plain SQL commands and ADO.Net to store the data. It wasn't until I hit a violation of a constraint that I discovered that, at least the way I am doing it, SQL has no concept of time zones: when I wrote a value executing a SQL INSERT (and providing the DATETIME as a string, like this: '8/23/2006 12:43:09 PM') and then when I tried to store the same thing using the ADO.Net SqlAdapter->Update(), it said it was not the same value! Well, it turns out my object was set to be local time, while when parsing it off of a string the kind is "unspecified". The funny thing is that it appears as though just before writing to the SQL Server, the ADO.Net methods do a conversion to local but it ultimately seems to be keeping it as UTC because only then my DateTime values would be different. I can live with that but if I'm missing something and I have the ability to store the time zone as well, I'd like to do that.
Thanks, Pat. But it turned out - that wasn't my problem. It was ... rounding errors! When I use INSERT, I supply a string that has no milliseconds in it but SQL Server supports millisecond values, just as the ADO.Net DateTime does. So when I send the value using Update(), it gets the full resolution. And hence the difference. Now the question becomes: how can I supply a DATETIME string that includes milliseconds - simply add them as a fraction after the seconds, right? As in '8/23/2006 12:43:09.714 PM'?
Yes, although I'd suggest using the ISO date/time format, such as '2006-08-23 12:49:09.714', and also keep in mind that SQL Server uses a 3 ms resolution while .NET frequently uses a 1 ms resolution. This can be maddening if you forget it (not that I've ever forgotten and spent hours trying to figure out what was wrong, I just read about this guy that did that once ).
I apologize for bringing this thread up from the dead for no good reason... but I was reading about dates and times and noticed that the ISO 8601 format for a combined date and time representation requires a time designator - the letter "T" - between the date and the time. Even the SQL Server books documentation uses it (e.g., the "Date and Time Formats" page). In such case a date/time combined field would be as follows: '2006-08-23T12:49:09.714'. But then the "Date and Time (Transact-SQL)" page shows an example at the bottom where a space is used instead of the "T". There is no doubt that the latter works, I just wonder how it came about having both options. And if I should go and correct all of my code that uses the space instead of the "T" to avoid any surprises with future versions of SQL Server or some other situation.