Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    London
    Posts
    169

    Unanswered: Dynamic sql help

    Hi there

    I am having problems using some dynamic sql for a crystal report, here is my code.

    DECLARE @startdate varchar(20)
    DECLARE @enddate varchar(20)
    SET @startdate = '11/01/2007'
    SET @enddate = '11/30/2007'

    SET @SQL = 'SELECT DATEADD(dd,CONVERT(int,CONVERT(int,F4111.ILDGL) % 1000),DATEADD(yyyy,CONVERT(int,F4111.ILDGL / 1000),CONVERT(datetime,''1900-01-01'')) )as ILDGL, F4111.ILTRDJ, F4111.ILKCO, F4111.ILDOC, F4111.ILDCT, F4111.ILDCTO, F4111.ILDOCO, F4111.ILITM, F4111.ILMCU, F4111.ILLITM, F4111.ILLOCN, F4111.ILLOTN, F4111.ILTRQT, F4111.ILTRUM, F4111.ILUNCS,F4102.IBSRP1, F4102.IBSRP2, F4101.IMDSC1 INTO ##F4111t FROM F4111 F4111 INNER JOIN F4102 F4102 ON F4111.ILMCU = F4102.IBMCU INNER JOIN F4101 F4101 ON F4111.ILITM=F4101.IMITM AND F4101.IMITM = F4102.IBITM WHERE DATEADD(dd,CONVERT(int,CONVERT(int,F4111.ILDGL) % 1000),DATEADD(yyyy,CONVERT(int,F4111.ILDGL / 1000),CONVERT(datetime,''1900-01-01'')) )<= ' + @enddate + ' And DATEADD(dd,CONVERT(int,CONVERT(int,F4111.ILDGL) % 1000),DATEADD(yyyy,CONVERT(int,F4111.ILDGL / 1000),CONVERT(datetime,''1900-01-01'')) ) >= ' + @startdate + ''

    exec (@SQL)

    This does not evaluate the dates properly and when I print the sql string I get the following:

    SELECT DATEADD(dd,CONVERT(int,CONVERT(int,F4111.ILDGL) % 1000),DATEADD(yyyy,CONVERT(int,F4111.ILDGL / 1000),CONVERT(datetime,'1900-01-01')) )as ILDGL, F4111.ILTRDJ, F4111.ILKCO, F4111.ILDOC, F4111.ILDCT, F4111.ILDCTO, F4111.ILDOCO, F4111.ILITM, F4111.ILMCU, F4111.ILLITM, F4111.ILLOCN, F4111.ILLOTN, F4111.ILTRQT, F4111.ILTRUM, F4111.ILUNCS,F4102.IBSRP1, F4102.IBSRP2, F4101.IMDSC1 INTO ##F4111t FROM F4111 F4111 INNER JOIN F4102 F4102 ON F4111.ILMCU = F4102.IBMCU INNER JOIN F4101 F4101 ON F4111.ILITM=F4101.IMITM AND F4101.IMITM = F4102.IBITM WHERE DATEADD(dd,CONVERT(int,CONVERT(int,F4111.ILDGL) % 1000),DATEADD(yyyy,CONVERT(int,F4111.ILDGL / 1000),CONVERT(datetime,'1900-01-01')) )<= 11/30/2007 And DATEADD(dd,CONVERT(int,CONVERT(int,F4111.ILDGL) % 1000),DATEADD(yyyy,CONVERT(int,F4111.ILDGL / 1000),CONVERT(datetime,'1900-01-01')) ) >= 11/01/2007

    So basically its has not putting ' ' around the date so its causing my code to fail.
    There are lots of strange date conversions in this code because I have a Julian date in my source data.

    How can I get this to work?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    .....)) ) >= ''' + @startdate + ''''
    HTH

  3. #3
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    Perfect! thanks matey

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •