Professional Web Applications Themes

Convert function error - Microsoft SQL / MS SQL Server

Hi, I am trying to find why the following code in Query yzer results in an error. If I directly use '20030806' in the Convert function, the error does not occur. I appreciate your help. Declare myDate nvarchar Set myDate = '20030806' select convert(datetime,myDate) -- Error! -------------------- Error message Server: Msg 241, Level 16, State 1, Line 4 Syntax error converting datetime from character string. Thanks, Tomtom...

  1. #1

    Default Convert function error

    Hi,

    I am trying to find why the following code in Query yzer results in an
    error. If I directly use '20030806' in the Convert function, the error does
    not occur. I appreciate your help.

    Declare myDate nvarchar
    Set myDate = '20030806'

    select convert(datetime,myDate) -- Error!

    --------------------
    Error message

    Server: Msg 241, Level 16, State 1, Line 4
    Syntax error converting datetime from character string.


    Thanks,
    Tomtom


    Tom Guest

  2. #2

    Default Re: Convert function error

    > Declare myDate nvarchar

    Why are you using nvarchar? And why do you not specify a length? How about
    using CHAR(8)?
     

    And in your existing code, try

    SELECT myDate

    You might see what's happening.


    Aaron Guest

  3. #3

    Default Re: Convert function error

    it appears that you need to specify a length when you declare your variable


    PRINT 'what you were trying to do'
    Declare myDate nvarchar
    Set myDate = '20030806'
    SELECT myDate
    select convert(datetime,myDate) -- Error!
    go
    PRINT ''
    PRINT 'this works better...'
    Declare myDate1 nvarchar(8)
    Set myDate1 = '20030806'
    SELECT myDate1
    select convert(datetime,myDate1)


    --
    Keith, SQL Server MVP

    "Tom Tom" <com> wrote in message news:phx.gbl... 
    Keith Guest

  4. #4

    Default Re: Convert function error

    Varchar and Char default to length 1.

    "Tom Tom" <com> wrote in message
    news:%phx.gbl... 
    first 
    variable [/ref]
    an 
    > does 
    >
    >[/ref]


    raydan Guest

  5. #5

    Default Re: Convert function error

    Thanks all. I forgot to specify the length. Didn't know that only the first
    letter is returned if I don't specify the length.

    Tomtom

    "Keith Kratochvil" <com> wrote in
    message news:phx.gbl...
    it appears that you need to specify a length when you declare your variable


    PRINT 'what you were trying to do'
    Declare myDate nvarchar
    Set myDate = '20030806'
    SELECT myDate
    select convert(datetime,myDate) -- Error!
    go
    PRINT ''
    PRINT 'this works better...'
    Declare myDate1 nvarchar(8)
    Set myDate1 = '20030806'
    SELECT myDate1
    select convert(datetime,myDate1)


    --
    Keith, SQL Server MVP

    "Tom Tom" <com> wrote in message
    news:phx.gbl... 
    does 


    Tom Guest

  6. #6

    Default Re: Convert function error

    I'm in a habit of using nvarchar because I use Japanese characters often and
    try to avoid any complications that are caused by using varchar. Or am I
    mistaken (again)?

    "Keith Kratochvil" <com> wrote in
    message news:phx.gbl...
    As Aaron mentioned, you probably do not need to use nvarchar...

    --
    Keith, SQL Server MVP

    "Tom Tom" <com> wrote in message
    news:%phx.gbl... 
    first 
    variable [/ref]
    an 
    > does 
    >
    >[/ref]


    Tom Guest

  7. #7

    Default Re: Convert function error

    When are Japanese characters going to be in an 8-character date string?

    Use nvarchar where you will *need* foreign alphabet support. Don't just use
    it everywhere out of habit.


    "Tom Tom" <com> wrote in message
    news:phx.gbl... 
    and 


    Aaron Guest

Similar Threads

  1. Convert error
    By Pedro_Remedios@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 1
    Last Post: April 14th, 09:30 PM
  2. function to convert into list ?
    By Babon in forum Macromedia Director Basics
    Replies: 1
    Last Post: November 29th, 03:22 AM
  3. note 33800 added to function.convert-cyr-string
    By php-general@lists.php.net in forum PHP Notes
    Replies: 1
    Last Post: July 8th, 07:16 AM
  4. Using CONVERT function
    By Bill in forum ASP
    Replies: 5
    Last Post: July 1st, 11:41 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