Post reply

weird date prob

  • July 4, 2005 at 11:44 am

    #167711

    this is driving me nuts - anybody know why i get the error...

    "Incorrect syntax near the keyword 'convert'."

    heres the code...

    declare @date datetime

    set @date = '1 aug 2005'
    exec("select '" + convert(varchar(20),@date,106) +

    "'")

     
    if i just do a select like...
    select "select '" + convert(varchar(20),@date,106) + "'"
     
    it produces this...
    select '04 Jul 2005'
     
    and thats what i wanna execute!
     
    any ideas
     
     
     
     

  • July 4, 2005 at 12:00 pm

    #570727

    That can't be the full code????

    Anyways this works :

    Declare @Exec as varchar(100)

    set @Exec = 'select ''' + convert(varchar(20),getdate(),106) + ''''

    exec(@Exec)

  • July 4, 2005 at 12:08 pm

    #570728

    no its not all of it - i just took out the relevant error

    Im passing dates to dynamic sql but it clearly doesnt like converted datetimes in the expression - i guess theres something going on under the hood!

    i used 2 extra varchars and its happy now - it'll have to do

    cheers matey!

  • July 4, 2005 at 12:11 pm

    #570729

    Why do you use dynamic sql anyways?

  • July 4, 2005 at 12:15 pm

    #570730

    lifted this from my worklog...

    Because of the nature of transaction behaviour sybase makes it prohibitive to add indices to # tables in a batch because the 'create index' clause cannot be compiled if the table in question does not exist. I therefore used dynamic sql and 'permenant' tables in tempdb to overcome these issues. Dynamic sql is built at run time and typically includes parameters/variables such as table names and helps overcome any issues with multiple use of the procs such as users attempting to use the same tables. The 'permenant' tables created in tempdb will be discarded at the end of report creation.

  • July 4, 2005 at 12:19 pm

    #570731

    I C.

    Tx.

Viewing 6 posts - 1 through 5 (of 5 total)

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