Deterministic

  • jwbart06

    Hall of Fame

    Points: 3291

    Comments posted to this topic are about the item Deterministic

  • Danny Ocean

    SSCertifiable

    Points: 6098

    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 !!!
    www.GrowWithSql.com

  • kapil_kk

    SSC-Insane

    Points: 21316

    For me this question is NON deterministic 😉

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

  • Mighty

    SSCrazy Eights

    Points: 8338

    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.

  • nenad-zivkovic

    Default port

    Points: 1446

    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)

  • Lokesh Vij

    SSChampion

    Points: 10836

    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

  • Carlo Romagnano

    SSC-Insane

    Points: 21665

    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

  • Koen Verbeeck

    SSC Guru

    Points: 258859

    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

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    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.

  • kapil_kk

    SSC-Insane

    Points: 21316

    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/

  • (Bob Brown)

    SSCrazy

    Points: 2705

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

  • venoym

    SSCarpal Tunnel

    Points: 4161

    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.

  • sburcombe

    SSCrazy

    Points: 2450

    Great question. I learned something today.

    Simone
  • sestell1

    SSChampion

    Points: 10230

    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.

  • m mcdonald

    Hall of Fame

    Points: 3180

    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 26 total)

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