Thomas Mueller As a comment to your recent blog article.

-
Thomas Mueller As a comment to your recent blog article.

The way SQL Server treats converting string to datetime is determined by its dateformat setting. See https://msdn.microsoft.com/en-us/library/ms189491.aspx

And I think the defaults are in fact ISO-8601 according to this article:
https://msdn.microsoft.com/en-us/library/ms186724.aspx

Be careful though with changing it as you might horribly break something.

Comments

  1. Thomas Mueller (dummzeuch)July 27, 2016 at 9:05 AM

    Thanks. I still think it is a very stupid idea to interpret a string that looks like ISO 8601 as something else. That's what standards are for.

    ReplyDelete
  2. Stefan GlienkeJuly 27, 2016 at 9:06 AM

    And that's why I cringe every time someone asks me "can we make it configurable?" ;)

    ReplyDelete
  3. Bill MeyerJuly 27, 2016 at 9:47 AM

    As a friend used to say: The nice thing about standards is that we have so many from which to choose.

    ReplyDelete
  4. A. BouchezJuly 27, 2016 at 1:02 PM

    Just use a date time parameter, not text. It will work on all dbs. And you would be able to prepare and reuse the statement.

    ReplyDelete
  5. Thomas Mueller (dummzeuch)July 27, 2016 at 2:43 PM

    A. Bouchez it's not about a Delphi program but fixing a database somebody else has broken.

    ReplyDelete
  6. Christoph HillefeldJuly 27, 2016 at 10:20 PM

    Instead of changing the DATEFORMAT globally another option is to explicitly use CONVERT every time you insert a date like this:
    CONVERT(DATETIME, '2008-02-14', 126)
    Where 126 tells the convert function to interpret the string as ISO8601. See https://msdn.microsoft.com/en-us/library/ms187928.aspx

    ReplyDelete
  7. A. BouchezJuly 28, 2016 at 1:37 AM

    Thomas Mueller Whats prevents you from fixing the database using a small program with a parameterized query?

    ReplyDelete
  8. Thomas Mueller (dummzeuch)July 28, 2016 at 1:52 AM

    A. Bouchez The customer's policy to forbid plugging in an USB stick and to run any non-vetted software. Also, when I went there, I wasn't even sure what the problem was. I had only a rather vague error message to go on and an idea what might be the cause (which turned out to be correct).

    ReplyDelete

Post a Comment