Deterministic

  • Comments posted to this topic are about the item Deterministic

  • Hi,

    Sorry, but i am unable to understand question completely.:ermm:

    If you increase varchar length then it will give right answer. Please try below T-sql statements

    SELECT CONVERT(VARCHAR,GETDATE(),20)

    SELECT CONVERT(VARCHAR,GETDATE(),21)

    SELECT CONVERT(VARCHAR,GETDATE(),106)

    SELECT CONVERT(VARCHAR,GETDATE(),107)

    SELECT CONVERT(VARCHAR,GETDATE(),109)

    SELECT CONVERT(VARCHAR,GETDATE(),113)

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • For me this question is NON deterministic 😉

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Danny Ocean (3/21/2013)


    Hi,

    Sorry, but i am unable to understand question completely.:ermm:

    If you increase varchar length then it will give right answer. Please try below T-sql statements

    SELECT CONVERT(VARCHAR,GETDATE(),20)

    SELECT CONVERT(VARCHAR,GETDATE(),21)

    SELECT CONVERT(VARCHAR,GETDATE(),106)

    SELECT CONVERT(VARCHAR,GETDATE(),107)

    SELECT CONVERT(VARCHAR,GETDATE(),109)

    SELECT CONVERT(VARCHAR,GETDATE(),113)

    The question is not about the statements executing correctly, but if the result of the CONVERT is deterministic. And that means that the CONVERT always should return the same result, independent of the language or any other setting. This is e.g. not the case with dates having month names in it.

  • SET LANGUAGE Italian

    not the greatest example as Marzo will also shorten to mar, try Czech, Croatian or Thai 🙂

    _______________________________________________
    www.sql-kefalo.net (SQL Server saveti, ideje, fazoni i fore)

  • kapil_kk (3/21/2013)


    For me this question is NON deterministic 😉

    I thought the same at first look. After bit of research, I got the curx and answered correctly 🙂

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • These conversions are non deterministic, but from string to date:

    from BOL:

    Certain datetime Conversions Are Nondeterministic in SQL Server 2005 and Later Versions

    In SQL Server 2000, string to date and time conversions are marked as deterministic. However, this is not true for the styles listed in the following table. For these styles, the conversions depend on the language settings. SQL Server 2005 and later versions mark these conversions as nondeterministic.

    The following table lists the styles for which the string-to-datetime conversion is nondeterministic.

    All styles below 100 (1)

    106 107 109 113 130

    (1) With the exception of styles 20 and 21

  • So a part of the correct answer is "all styles below 100" are non-deterministic, but another answer is "not 20,21", because they are deterministic...

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Thank you for posting, good one.

    Never digged so deep on this area, was interesting to learn.

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Raghavendra Mudugal (3/22/2013)


    Thank you for posting, good one.

    Never digged so deep on this area, was interesting to learn.

    +1 🙂

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Great question. Didn't know the answer off the top of my head but dug into the CONVERT command and got it. Thanks.

  • May I be the first to point to BOL for Non-Deterministic?

    http://msdn.microsoft.com/en-us/library/ms187928(v=sql.100).aspx

    Take a look at the Remarks section. There is a table with superscript "1"'s in it... All of the "1"'s are defined as Non-Deterministic.

    So, According to BOL, the values:

    20, 21, 101, 102, 103, 104, 105, 108, 110, 111, 112, 114, 126, 127, 131 are deterministic, all others are non-deterministic.

  • Great question. I learned something today.

    Simone
  • Interesting question.

    I knew what the question was asking but got hung up on the wording of the answers for a bit. I think I would have phrased things a bit differently, so you don't have to infer "except..." at the end of the first selection.

  • The way I read there are only 2 correct answers:

    not 106,107,109,113

    20,21

    The answer, and included as a correct answer

    Styles greater than 100

    Cannot be correct because of the exceptions 106,107,109,113.

    The answer appears to have been incorrectly parsed...

    From http://msdn.microsoft.com/en-us/library/ms178091.aspx

    Source or target type is datetime or smalldatetime, the other source or target type is a character string, and a nondeterministic style is specified. To be deterministic, the style parameter must be a constant. Additionally, styles less than or equal to 100 are nondeterministic, except for styles 20 and 21. Styles greater than 100 are deterministic, except for styles 106, 107, 109 and 113.

    </my 2 cents>

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply