locked
DATEADD getting a Syntax error RRS feed

  • Question

  • I am trying to change my Promise Date when the SD.Status = 'Hold' to add 100 years so that when it sorts it it comes out at the bottom of the extract. I want the Promised Date to not change if the SD.Status is not equal to 'Hold'. The Sd.Promise_Date field is a DateTime field. I do not see my error. See my code and error message below.

    Select SD.Sales_Order                                              As [SO #]
          ,SD.SO_Line                                                        As [Ln]
          ,Cast(Min(SD.Status) As Varchar(1))                   As [SS] 
          ,Min(SH.Sales_Rep)                                            As [Rep]
          ,Min(SH.Customer)                                             As [Cust ID]
          ,Min(SD.Material)                                                As [Material/Part Number]
          ,Min(Case when SD.Material = 'CUSTOM-SPECIAL' then (Substring(SD.EXT_Description,1,30))
                  else M.Description end)                             As [Description]
          ,Min(Case when SD.Status = 'Hold'                                             then 'HOLD'
                  when PATINDEX('%Ship complete%', SH.Comment)>0       then 'SC'
                  when PATINDEX('%In CustomerID%', SH.Comment)>0      then 'ICSL'
                  when SH.Ship_Via = 'Cust Stock'                                        then 'CSTK'
             when SH.Ship_Via = 'Install'                                           then 'INST'
         else '' end)                                                     [Txt]
    ,Min(Case when SD.Job Is Not null                                           then SD.Job          
                  when PATINDEX('%SO Given to RonF%',  SH.Note_Text)>0 then 'RON F'
                  when PATINDEX('%SO Given to Cut%',   SH.Note_Text)>0 then 'CUT'
                  when PATINDEX('%SO Given to Joe%',   SH.Note_Text)>0 then 'JOE'
                  when PATINDEX('%SO Given to Laser%', SH.Note_Text)>0 then 'LASER'
         when SD.Job IS null                                  then ''
         else '' end)                                                    [Linked]
    ,Min(ISNULL(SD.PO,''))                                                 As [PO]  
    ,Cast(MIN(ISNULL(J.Status,''))As Varchar(1))               As [St]
    ,Cast(SUM((SD.Order_Qty - SD.Shipped_Qty) * (100 - SD.Discount_Pct) * SD.Unit_Price / 100) As Decimal(7,2)) As [Ext Amt]
    ,Min(SD.Order_Qty)                                         As [Ord]
    ,Min(SD.Picked_Qty)                                        As [Pick]
    ,Min(SD.Backorder_Qty)                                  As [BO]
    ,Cast(Min(SH.Order_Date) As Date)                As [Ord Date]
    ,Min(M.Lead_Days)                                          As [LD]
    ,(Case when SD.Status = 'Hold'
          then DATEADD(yyyy,100,'SD.Promised_Date')
          else DATEADD(yyyy,0,'SD.Promised_Date') PromDate end)
    ,Cast(PromDate as Date)                                    As [Promised]
      from [PRODUCTION].dbo.SO_Detail As SD
    INNER JOIN [PRODUCTION].dbo.SO_Header As SH
    on SD.Sales_Order = SH.Sales_Order
    and (SD.Status = 'Open' 
             or  SD.Status = 'Backorder'
             or  SD.Status = 'Hold' ) 
    INNER JOIN [PRODUCTION].dbo.Material As M
    on SD.Material = M.Material
    LEFT OUTER JOIN [PRODUCTION].dbo.Job As J
    On SD.Job = J.Job
    Group by SD.Promised_Date, SD.Sales_Order, SD.SO_Line
    Order by SD.Promised_Date, SD.Sales_Order, SD.SO_Line

    Msg 102, Level 15, State 1, Line 32
    Incorrect syntax near 'PromDate'.


    SWProduction

    Monday, June 9, 2014 12:31 PM

Answers

  • You put the column's name into quotes, which is wrong.

    Change this

     DATEADD(yyyy,100,'SD.Promised_Date')

    to

     DATEADD(year,100,SD.Promised_Date)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by SWProduction Monday, June 9, 2014 3:29 PM
    Monday, June 9, 2014 3:20 PM
  • OK thanks.... that made the code execute and it changed the output Promised_Date but it appears to have done it after the Group and Order by processes. Is there a way to make this happen before the Group and Order by processes take effect. I am looking for output to be:

    2014-05-30

    2014-06-01

    2114-06-01

    2014-06-21

    2114-06-22

    etc.

    it appears now as:

    2014-05-30

    2114-06-01

    2014-06-01

    2014-06-21

    2114-06-22


    SWProduction

    for that you need to do changes in GROUP BY and ORDER BY as well

    as below

    Group by DATEADD(yyyy,CASE WHEN SD.Status = 'Hold' THEN 1000 ELSE 0 END,SD.Promised_Date), SD.Sales_Order, SD.SO_Line
    Order by DATEADD(yyyy,CASE WHEN SD.Status = 'Hold' THEN 1000 ELSE 0 END,SD.Promised_Date), SD.Sales_Order, SD.SO_Line


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by SWProduction Monday, June 9, 2014 6:08 PM
    Monday, June 9, 2014 5:02 PM
    Answerer
  • Hi ,

      Change the code for promised column as below

    ,Cast(
    	  Case when SD.Status = 'Hold'
          then DATEADD(yyyy,100,'SD.Promised_Date')
          else DATEADD(yyyy,0,'SD.Promised_Date') 
    	  end as Date)   As [Promised]



    Best Regards Sorna

    • Marked as answer by SWProduction Monday, June 9, 2014 3:35 PM
    Monday, June 9, 2014 12:58 PM

All replies

  • Hi ,

      Change the code for promised column as below

    ,Cast(
    	  Case when SD.Status = 'Hold'
          then DATEADD(yyyy,100,'SD.Promised_Date')
          else DATEADD(yyyy,0,'SD.Promised_Date') 
    	  end as Date)   As [Promised]



    Best Regards Sorna

    • Marked as answer by SWProduction Monday, June 9, 2014 3:35 PM
    Monday, June 9, 2014 12:58 PM
  • It looks like we are getting closer. Here is the error message that I am now receiving:

    Msg 241, Level 16, State 1, Line 1
    Conversion failed when converting date and/or time from character string.


    SWProduction

    Monday, June 9, 2014 1:34 PM
  • You put the column's name into quotes, which is wrong.

    Change this

     DATEADD(yyyy,100,'SD.Promised_Date')

    to

     DATEADD(year,100,SD.Promised_Date)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by SWProduction Monday, June 9, 2014 3:29 PM
    Monday, June 9, 2014 3:20 PM
  • OK thanks.... that made the code execute and it changed the output Promised_Date but it appears to have done it after the Group and Order by processes. Is there a way to make this happen before the Group and Order by processes take effect. I am looking for output to be:

    2014-05-30

    2014-06-01

    2114-06-01

    2014-06-21

    2114-06-22

    etc.

    it appears now as:

    2014-05-30

    2114-06-01

    2014-06-01

    2014-06-21

    2114-06-22


    SWProduction

    Monday, June 9, 2014 3:34 PM
  • Change something like below:

    ,Case when SD.Status = 'Hold'
          then DATEADD(yyyy,100,SD.Promised_Date)
          else DATEADD(yyyy,0,SD.Promised_Date) end PromDate 
    ,Cast((Case when SD.Status = 'Hold'
          then DATEADD(yyyy,100,SD.Promised_Date)
          else DATEADD(yyyy,0,SD.Promised_Date) end) as Date)                                    As [Promised]

    Monday, June 9, 2014 3:37 PM
    Answerer
  • I think, am bit late....glad to see you solved your issue though!!
    Monday, June 9, 2014 3:38 PM
    Answerer
  • OK thanks.... that made the code execute and it changed the output Promised_Date but it appears to have done it after the Group and Order by processes. Is there a way to make this happen before the Group and Order by processes take effect. I am looking for output to be:

    2014-05-30

    2014-06-01

    2114-06-01

    2014-06-21

    2114-06-22

    etc.

    it appears now as:

    2014-05-30

    2114-06-01

    2014-06-01

    2014-06-21

    2114-06-22


    SWProduction

    Monday, June 9, 2014 3:50 PM
  • OK thanks.... that made the code execute and it changed the output Promised_Date but it appears to have done it after the Group and Order by processes. Is there a way to make this happen before the Group and Order by processes take effect. I am looking for output to be:

    2014-05-30

    2014-06-01

    2114-06-01

    2014-06-21

    2114-06-22

    etc.

    it appears now as:

    2014-05-30

    2114-06-01

    2014-06-01

    2014-06-21

    2114-06-22


    SWProduction

    for that you need to do changes in GROUP BY and ORDER BY as well

    as below

    Group by DATEADD(yyyy,CASE WHEN SD.Status = 'Hold' THEN 1000 ELSE 0 END,SD.Promised_Date), SD.Sales_Order, SD.SO_Line
    Order by DATEADD(yyyy,CASE WHEN SD.Status = 'Hold' THEN 1000 ELSE 0 END,SD.Promised_Date), SD.Sales_Order, SD.SO_Line


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by SWProduction Monday, June 9, 2014 6:08 PM
    Monday, June 9, 2014 5:02 PM
    Answerer