Post reply

getdate() in dynamic SQL

  • May 26, 2008 at 4:13 pm

    #76994

    I would like to use the value of GetDate() within a dynamic SQL statement.

    THe following line:

    ', DateUpdated = '+ GetDate()+ CHAR(13)+

    produces a syntax error "error converting datetime from character string"

    Thanks for the help.

    Sam

  • May 26, 2008 at 5:27 pm

    #820294

    Try this:

    ', DateUpdated = '+ CAST(GetDate() as varchar(24))+ CHAR(13)+

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • May 26, 2008 at 5:28 pm

    #820295

    Though looking at it I think that you will need more apostrophes...

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • May 27, 2008 at 2:17 am

    #820405

    smknox (5/26/2008)

    I would like to use the value of GetDate() within a dynamic SQL statement.

    THe following line:

    ', DateUpdated = '+ GetDate()+ CHAR(13)+

    produces a syntax error "error converting datetime from character string"

    Thanks for the help.

    Sam

    Try This

    ', DateUpdated = '''+ CONVERT(VARCHAR(12),GETDATE(),101)+''''+ CHAR(13) +

    Prasad Bhogadi
    www.inforaise.com

  • May 27, 2008 at 2:21 am

    #820406

    You may want to use the convert function based on how you want to save the output of GETDATE() function in your DateUpdated Column.

    Prasad Bhogadi
    www.inforaise.com

  • May 27, 2008 at 4:28 am

    #820441

    ', DateUpdated = GetDate() ' + CHAR(13)

    🙂

    _____________
    Code for TallyGenerator

  • May 27, 2008 at 4:35 am

    #820445

    Sergiy (5/27/2008)

    ', DateUpdated = GetDate() ' + CHAR(13)

    🙂

    This is straight forward. We were trying to re-invent the wheel 🙂

    Prasad Bhogadi
    www.inforaise.com

  • May 27, 2008 at 6:35 am

    #820509

    Thanks! Sergei's syntax was it!

    Sam

  • June 12, 2008 at 10:30 pm

    #828236

    One very small thing: unless you're going to be somehow running the dynamic SQL string on a pre-OSX Mac(!) I'd use the "correct" Windows CRLF sequence, which is "char(13) + char(10)" rather than just plain "char(13)".

    Doesn't make a difference to the SQL parser, but it's cleaner if you're going to be visualising the string in most Windows apps.

    Regards,

    Jacob

Viewing 9 posts - 1 through 8 (of 8 total)

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