Clovertech

SQL Table update Query and formatting from a string to a date

› Clovertech Forums › Cloverleaf › SQL Table update Query and formatting from a string to a date

  • Creator
    Topic
  • October 7, 2024 at 11:51 am #121629
    Rin
    Participant

      Hi,

      How can format the query so that sql database interprets it correctly as the actual date when UPDATING a database from an incoming file (string)?

      Output from logs:

      IMS_TCHEADER.TCHEADERAGRMNTDATE= 2018-10-02,

      *********************************************

      In Cloverleaf it is configured as below:

      UPDATE IMS_TCHEADER
      SET IMS_TCHEADER.TCHEADERAGRMNTDATE=<IMS_TCHEADER.TCHEADERAGRMNTDATE>,

      IMS_TCHEADER.RowUpdateDateTime= GETDATE()

      WHERE
      TCHEADERNATVENDORNBR=<IMS_TCHEADER.TCHEADERNATVENDORNBR> and TCHEADERCTRCTNBR=<IMS_TCHEADER.TCHEADERCTRCTNBR>

      Thanks.

       

    • Creator
      Topic
    Viewing 0 reply threads
    • Author
      Replies
      • October 14, 2024 at 2:49 pm #121637
        Rin
        Participant

          Hi,

          Can someone please tell me what the issue is with my query below?

          Error string “BADDATA caught exception trying to processMessageFromCloverleaf: Failed to write message to database: Incorrect syntax near ‘<‘.”. Returning bad send to engine.

          UPDATE IMS_TCHEADER

          SET

          IMS_TCHEADER. FILLER=NULL,

          IMS_TCHEADER.FILLER1=’Y’, IMS_TCHEADER.RowUpdateDateTime=GETDATE(), IMS_TCHEADER.TCHEADERACTINACTIND=<‘IMS_TCHEADER.TCHEADERACTINACTIND’>, IMS_TCHEADER.TCHEADERAGRMNTDATE=<‘IMS_TCHEADER.TCHEADERAGRMNTDATE’>, IMS_TCHEADER.TCHEADERCONSIGNAVAIL=NULL, IMS_TCHEADER.TCHEADERCTRCTEFFDATE=<‘IMS_TCHEADER.TCHEADERCTRCTEFFDATE’>, IMS_TCHEADER.TCHEADERCTRCTEXPDATE=<‘IMS_TCHEADER.TCHEADERCTRCTEXPDATE’>, IMS_TCHEADER.TCHEADERCTRCTNBR=<‘IMS_TCHEADER.TCHEADERCTRCTNBR’>, IMS_TCHEADER.TCHEADERCTRCTREVDATE=<‘IMS_TCHEADER.TCHEADERCTRCTREVDATE’>, IMS_TCHEADER.TCHEADERCTRCTSTATUS=<‘IMS_TCHEADER.TCHEADERCTRCTSTATUS’>, IMS_TCHEADER.TCHEADERCTRCTTYPE=<‘IMS_TCHEADER.TCHEADERCTRCTTYPE’>, IMS_TCHEADER.TCHEADERCTRTAVAILABLE=<‘IMS_TCHEADER.TCHEADERCTRTAVAILABLE’>, IMS_TCHEADER.TCHEADERCTRTCLASS=<‘IMS_TCHEADER.TCHEADERCTRTCLASS’>, IMS_TCHEADER.TCHEADERDISCAPPLIEDTO=NULL, IMS_TCHEADER.TCHEADERDISTCOORDNAME=NULL, IMS_TCHEADER.TCHEADERDISTCOORDPHONE=NULL, IMS_TCHEADER.TCHEADEREDITERMSA=NULL, IMS_TCHEADER.TCHEADEREDITERMSB=NULL, IMS_TCHEADER.TCHEADEREDITERMSCODE=’5′, IMS_TCHEADER.TCHEADEREDITERMSDAYS=’0′, IMS_TCHEADER.TCHEADEREDITERMSDISC=’0′, IMS_TCHEADER.TCHEADERFOB=<‘IMS_TCHEADER.TCHEADERFOB’>, IMS_TCHEADER.TCHEADERMARKUPFLAG=’N’, IMS_TCHEADER.TCHEADERMFRCOORDNAME=NULL, IMS_TCHEADER.TCHEADERMFRCOORDPHONE=NULL, IMS_TCHEADER.TCHEADERMINIMUMORDERAMT=’0′, IMS_TCHEADER.TCHEADERMINIMUMORDERQTY=’0′, IMS_TCHEADER.TCHEADERNATVENDORNBR=<‘IMS_TCHEADER.TCHEADERNATVENDORNBR’>, IMS_TCHEADER.TCHEADERNBRRENEWALS=’0′, IMS_TCHEADER.TCHEADERORDERVENDORNBR=NULL, IMS_TCHEADER.TCHEADERPRICINGFIRMTHRU=<‘IMS_TCHEADER.TCHEADERPRICINGFIRMTHRU’>, IMS_TCHEADER.TCHEADERPRODUCTGROUPING=<‘IMS_TCHEADER.TCHEADERPRODUCTGROUPING’>, IMS_TCHEADER.TCHEADERPUOM=NULL, IMS_TCHEADER.TCHEADERREBATECODE=’N’, IMS_TCHEADER.TCHEADERREMARKS1=NULL, IMS_TCHEADER.TCHEADERREMARKS2=NULL, IMS_TCHEADER.TCHEADERREMARKS3=NULL, IMS_TCHEADER.TCHEADERTERMS=<‘IMS_TCHEADER.TCHEADERTERMS’>, IMS_TCHEADER.TCHEADERTERMSA=NULL, IMS_TCHEADER.TCHEADERTERMSB=’Not Applicable’, IMS_TCHEADER.TCHEADERTERMSCODE=’5′, IMS_TCHEADER.TCHEADERTERMSDAYS=’0′, IMS_TCHEADER.TCHEADERTERMSDISC=’0′, IMS_TCHEADER.TCHEADERTERMSFOB=’4′, IMS_TCHEADER.TCHEADERVALUEADDS=NULL WHERE IMS_TCHEADER.TCHEADERNATVENDORNBR=<‘IMS_TCHEADER^TCHEADERNATVENDORNBR’> and IMS_TCHEADER.TCHEADERCTRCTNBR=<‘IMS_TCHEADER^TCHEADERCTRCTNBR’>

           

      • Author
        Replies
      Viewing 0 reply threads
      • You must be logged in to reply to this topic.