Post reply

Dynamic SQL String

  • April 12, 2005 at 3:49 pm

    #68827

    Hi,

    This statement fragment works in query analyzer:

    Age=DateDiff(d,CS.AdmSrvDate,GetDate())

    but when I try to add it to a dynamic sql string by appending it to @strsql in a stored procedure, like this:

    SET @strSQL = @strSQL + ' DateDiff(d,CS.AdmSrvDate, ' + GetDate() + ')'

    I get the error:

    Syntax error converting datetime from character string.

    Am I doing something wrong with the single quotes?

    Thank you!

  • April 12, 2005 at 4:34 pm

    #552261

    Brenna, try

    SET @strSQL = @strSQL + ' DateDiff(d,CS.AdmSrvDate, + GetDate() + )'

    Just a couple less single quotes.

    HTH

  • April 12, 2005 at 4:42 pm

    #552263

    Thanks!

    What if I want to use a variable in the DTS, for example, @RptDate, instead of GetDate()?

    Brenna

     

  • April 13, 2005 at 1:15 am

    #552309

    Since DATEDIFF returns an Integer value, SQL Server tries to cast your @strSQL also to an INT, because INT has a higher precedence. What about this?

    DECLARE @strSQL NVARCHAR(50)

    DECLARE @MyDate DATETIME

    Set @MyDAte=GETDATE()

    SET @strSQL= 'The Int value of today is: '

    PRINT @strSQL+ CAST(DateDiff(d,0,@MyDate) AS NVARCHAR(5))

    The Int value of today is: 38453

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • April 13, 2005 at 3:12 am

    #552327

    Why messing with dynamic SQL in the first place?

    What is the purpose of this exercise? Does it have to be dynamic?

    /Kenneth

  • April 13, 2005 at 8:23 am

    #552425

    The larger statement as a whole needs to be dynamic. I'm using a stored procedure as a datasource for a Crystal report.  The joins in the SQL statement need to change based on which parameters the user chooses at run time.  Is there a better way to do this?

    I want the datasource to include a column with the age of each account in days, from a given date (AdmSrvDate) to today.

  • April 13, 2005 at 9:52 am

    #552467

    Thank you, Frank!  This worked:

     

    SET @strSQL = @strSQL + ' CAST(DateDiff(d,CS.AdmSrvDate,  GetDate()) AS nvarchar(5))'

  • April 13, 2005 at 10:52 am

    #552491

    Ooops.  Forgot that I wanted to calculate age from @RptDate, not GetDate().

    This works:

    SET @strSQL = @strSQL + ' CAST(DateDiff(d,CS.AdmSrvDate, ' + '''' + CONVERT(varchar,@RptDate,101) +''''+ ') AS nvarchar(5)) END'

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

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