Professional Web Applications Themes

Syntax problem in exec statement - Microsoft SQL / MS SQL Server

Hi, I've got a straight forward enough SQL script. ---------------------------------------------------------------------------- -------------------------- declare datefrom smalldatetime declare dateto smalldatetime declare sqlcom set datefrom = '12/07/02' set dateto = '12/07/04' select * from testdb where libdate between datefrom and dateto ---------------------------------------------------------------------------- ---- This works fine. However, if I change that sql select to ; set sqlcom = "select * from testdb where libdate between " + datefrom + " and " dateto exec (sqlcom) it falls over with ; Syntax error converting character string to smalldatetime data type. I suspect it's something straight forward enough. Any thoughts ? Regards. Mike. Using SQL7, nt4 ...

  1. #1

    Default Syntax problem in exec statement

    Hi,

    I've got a straight forward enough SQL script.

    ----------------------------------------------------------------------------
    --------------------------

    declare datefrom smalldatetime
    declare dateto smalldatetime
    declare sqlcom

    set datefrom = '12/07/02'
    set dateto = '12/07/04'

    select * from testdb where libdate between datefrom and dateto
    ----------------------------------------------------------------------------
    ----

    This works fine. However, if I change that sql select to ;

    set sqlcom = "select * from testdb where libdate between " + datefrom + "
    and " dateto
    exec (sqlcom)

    it falls over with ;

    Syntax error converting character string to smalldatetime data type.

    I suspect it's something straight forward enough. Any thoughts ?

    Regards.
    Mike.

    Using SQL7, nt4 sp6a


    Mike Guest

  2. #2

    Default Syntax problem in exec statement

    Hi Mike,

    There might be a couple of issue here.
    1. you may need to set the date format
    set dateformat dmy

    2. I'm not sure if double quotes (") work around strings
    in SQL - I always use single quotes (')

    3. You need to convert the smalldatetime to CHAR or
    VARCHAR to concatenate it to a string. You mad need to use
    a style with the convert function - have a look at Convert
    in the SQL books online.

    4. You need to enclose the dates in single quotes.

    Try This:

    declare datefrom smalldatetime
    declare dateto smalldatetime
    declare sqlcom varchar(500)

    --set dateformat dmy

    set datefrom = '12/07/02'
    set dateto = '12/07/04'

    set sqlcom = 'select * from testdb where libdate
    between ''' + Convert(varchar(30), datefrom) + '''
    and ''' + Convert(varchar(30), dateto) + ''''

    print sqlcom
    exec (sqlcom)

    HTH,
    Eric
     
    ------------------ 
    dateto 
    ------------------ 
    to ; 
    between " + datefrom + " 
    smalldatetime data type. 
    thoughts ? 
    Eric Guest

  3. #3

    Default Re: Syntax problem in exec statement

    Mike
    try it
    declare datefrom as smalldatetime
    declare dateto as smalldatetime


    set datefrom = '20021010'
    set dateto = '20030730'


    declare sql varchar(100)
    set sql='select * from table_name where opendate between '+
    convert(varchar(30),datefrom,103)
    +' and '+convert(varchar(30),dateto, 103)
    print sql
    exec(sql)





    "Mike Gorton" <com> wrote in message
    news:OgBOj5$phx.gbl... 
    -- 
    -- 



    Uri Guest

Similar Threads

  1. Syntax error in UPDATE statement.
    By chill84 in forum Coldfusion Database Access
    Replies: 9
    Last Post: November 9th, 03:09 PM
  2. Syntax error in Where Statement
    By Battle in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 4th, 09:57 PM
  3. Help With Multiple If Statement Syntax
    By Rob Solberg in forum Macromedia Flash Actionscript
    Replies: 1
    Last Post: January 26th, 04:51 AM
  4. Syntax Error In Update Statement
    By MDW in forum ASP Database
    Replies: 10
    Last Post: October 13th, 10:07 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