Professional Web Applications Themes

CAST & CONVERT nondeterministic - Microsoft SQL / MS SQL Server

Why are these function nondeterministic when the result is a datetime??? Brandon -- "In the beginning the universe was created. This has made a lot of people very angry, and has been widely regarded as a bad move." - Douglas Noel Adams (1952-2001)...

  1. #1

    Default CAST & CONVERT nondeterministic

    Why are these function nondeterministic when the result is a
    datetime???

    Brandon
    --
    "In the beginning the universe was created. This has made a lot of
    people very angry, and has been widely regarded as a bad move." -
    Douglas Noel Adams (1952-2001)


    Brandon Lilly Guest

  2. #2

    Default Re: CAST & CONVERT nondeterministic

    Because the DATETIME results depend on the regional DATEFORMAT and LANGUAGE
    settings.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Brandon Lilly" <brandon.lillynospam_medevolve.com> wrote in message
    news:O4kFVzKQDHA.3768tk2msftngp13.phx.gbl...
    > Why are these function nondeterministic when the result is a
    > datetime???
    >
    > Brandon
    > --
    > "In the beginning the universe was created. This has made a lot of
    > people very angry, and has been widely regarded as a bad move." -
    > Douglas Noel Adams (1952-2001)
    >
    >

    David Portas Guest

  3. #3

    Default Re: CAST & CONVERT nondeterministic

    "David Portas" <REMOVE_BEFORE_REPLYING_dportasacm.org> wrote in
    message news:%23KWV93KQDHA.2480tk2msftngp13.phx.gbl...
    > Because the DATETIME results depend on the regional DATEFORMAT and
    LANGUAGE
    > settings.
    I can see where ISDATE is non-deterministed when used with CONVERT,
    but only with specific styles. I don't see how the following can be
    non-deterministic:

    CAST(CONVERT(char(8), Date_Time_Column, 112) AS datetime)

    It can only be one result. Ever.

    More mysteriously, while I cannot use the above convert in an indexed
    view (because it is supposedly nondeterministic), I CAN do the
    following and it works:

    DATEADD(dd, 0, CONVERT(char(8), Date_Time_Column, 112))

    Can anyone say "kludge"? It works wonderfully, but, man, it's
    hideous. It's deterministic if I let SQL implicitly convert the value
    to a datetime, but it is non-deterministic if I explicitly convert it
    to a datetime? SQL Server is prejudice! :)

    Can you think of any other way to convert that string to a date,
    perhaps one more efficient, or do you think this is not really
    affecting efficiency?

    Brandon
    --
    "In the beginning the universe was created. This has made a lot of
    people very angry, and has been widely regarded as a bad move." -
    Douglas Noel Adams (1952-2001)


    Brandon Lilly Guest

  4. #4

    Default Re: CAST & CONVERT nondeterministic

    If you use CONVERT with the style option rather than CAST, then it is
    deterministic:

    CONVERT(DATETIME, CONVERT(CHAR(8), Date_Time_Column, 112) ,112)

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Portas Guest

  5. #5

    Default Re: CAST & CONVERT nondeterministic

    "David Portas" <REMOVE_BEFORE_REPLYING_dportasacm.org> wrote in
    message news:OJ%23xxJLQDHA.1608TK2MSFTNGP11.phx.gbl...
    > If you use CONVERT with the style option rather than CAST, then it
    is
    > deterministic:
    >
    > CONVERT(DATETIME, CONVERT(CHAR(8), Date_Time_Column, 112) ,112)
    AHA! I didn't try that - two converts in a row looked odd, so I used
    CAST instead. Thanks for the clear-up.

    Seems MS needs to clarify that in their doentation about
    determinism and the CONVERT function, as it is obviously wrong.

    Thanks again!

    Brandon
    --
    "In the beginning the universe was created. This has made a lot of
    people very angry, and has been widely regarded as a bad move." -
    Douglas Noel Adams (1952-2001)


    Brandon Lilly Guest

Similar Threads

  1. Replies: 1
    Last Post: November 16th, 12:18 AM
  2. Replies: 1
    Last Post: November 7th, 02:18 AM
  3. cast members moved in cast - neet to get htem back!!!
    By spdorsey webforumsuser@macromedia.com in forum Macromedia Director Basics
    Replies: 2
    Last Post: November 24th, 08:48 PM
  4. changing the cast number of a cast at authoring time?
    By david taylor in forum Macromedia Director Basics
    Replies: 0
    Last Post: November 21st, 05:41 AM
  5. Do I need to Convert with Convert.ToInt32(session("myNumber")) ?
    By Andreas Klemt in forum ASP.NET General
    Replies: 1
    Last Post: July 23rd, 02:59 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139