1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

MSG-242 err

Discussion in 'Other Databases' started by sowmyav, Jan 16, 2009.

  1. sowmyav

    sowmyav Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Hi,
    I got the error as follows
    for the below sql
    Code (Text):

    SELECT Cast((Substring(ARR.Calendar_Month_N,5,2) + '-01-' + Substring(ARR.Calendar_Month_N,1,4)) as DateTime)
    > (Select Max(DateAdd(Month,-12,Cast((Substring(ARR.Calendar_Month_N,5,2) + '-01-' + Substring(ARR.Calendar_Month_N,1,4)) as DateTime))))
    FROM ADATA_200811 arr
    as I want only the last 12 months of data.
    This actually comes in the where condition,but as this is not working, I made as seperate select and cheking it now.

    Could anyone please help me on this?
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Hi, I am not very good with sql server but a single look at your query reveals many errors. Try this (not tested)
    Code (Text):

    /* Formatted on 2009/01/16 14:12 (Formatter Plus v4.8.8) */
    SELECT CAST ((  substring (arr.calendar_month_n, 5, 2)
                  + '-01-'
                  + substring (arr.calendar_month_n, 1, 4)
                 ) AS datetime
                ) xx
      FROM adata_200811 arr
     WHERE xx >
              (SELECT MAX
                         (dateadd
                              (mmonth,
                               -12,
                               CAST (  substring (arr.calendar_month_n, 5, 2)
                                     + '-01-'
                                     + substring (arr.calendar_month_n, 1, 4) AS datetime
                                    )
                              )
                         )
                 FROM adata_200811 arr)