Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2005
    Posts
    74

    Question Unanswered: Timezone of DATETIME data type

    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.
    Kamen

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The SQL Server DATETIME data type has no concept of timezone. Times are stored to roughly 3 millisexcond resolution from 1753-01-01 through 9999-12-31 23:59:59.997 (inclusive).

    The SQL function GetUTCDate() function returns UTC, and I STRONGLY recommend using it over the more common GetDate() function, especially for apps that have any potential of becoming international.

    -PatP

  3. #3
    Join Date
    Dec 2005
    Posts
    74
    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'?

    Kamen

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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 ).

    -PatP

  5. #5
    Join Date
    Dec 2005
    Posts
    74
    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.
    Kamen

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •