Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 13958
  • Last Modified:

SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007

Hello everyone.
  I'm a newbie here...so go easy on me.  I have the following code in a .cmd file ( sdv.cmd ).  I run it from a command prompt like this c:\sdv.cmd >h:\projects\sdv.txt.  It runs fine and it does what i want it to except that I want it to prompt for a date so I don't have to hard code the SQL date to a differnet date each day.  

How do a set a arg for the date and have it prompt the user to enter a date.

We are using a DB2 database.  ( sorry, that's all I know )


/***************************************************************************************
* THIS WILL EXTRACT DATA                                                                                                                                  *
*                                                                                                                                                                          *
***************************************************************************************/

'Dbmucmd mydatabase "select',
      'c.cust_rt_nbr,',
        'a.ord_appv_deliv_dt,',
        'a.ord_doc_nbr,',
        'd.hhc_descp_txt,',
        'd.selling_code,',
        'b.ord_orig_item_qty,',
        'b.ord_curr_item_qty,',
        'b.ship_recv_item_qty,',
        'b.ord_item_um_code,',
        'd.sdv_amt,',
        'e.unit_per_case_qty,',
        'e.cdy_per_case_qty,',
        'a.acctg_dest_loc_nm',
 'from oms.ord_ship_hdr a,',
        'oms.ord_ship_ln b,',
        'oms.ord_cust_rt c,',
        'slp.product_code_attr d,',
        'iim.sku e',
 'where a.ord_cancl_flg=''0'' and',
        'a.ord_delte_flg=''0'' and',
        'a.purge_flg=''0'' and',
        'c.rt_type=''01'' and',
        'a.ord_appv_deliv_dt=''2004-08-30'' and', <<<<<<<<<<<<<<<<< (This date )
        'a.ord_doc_nbr=b.ord_doc_nbr and',
        'a.ord_doc_nbr=c.ord_doc_nbr and',
        'b.prod_nbr=e.prod_nbr and',
        'b.case_seq_nbr=e.case_seq_nbr and',
        'b.prod_nbr=d.prod_code and',
        'd.PROD_C_ATTR_END_DT>=a.ORD_APPV_DELIV_DT ',
        ' AND A.ORD_TYPE_CODE<>''66'' ',
        ' AND A.ORD_TYPE_CODE<>''65'' ',
   'order by c.cust_rt_nbr,',
           'a.ord_appv_deliv_dt,',
           'a.ord_doc_nbr,',
           'd.selling_code"'

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

Thanks a million
     
 
0
awehme
Asked:
awehme
  • 3
  • 2
1 Solution
 
awehmeAuthor Commented:
Sorry, forgot the other part.  I have stole this code from another program we have and have placed in front of the sql above, but when I do that I get the following error

SQL0180N  The syntax of the string representation of a datetime value is incorrect.  SQLSTATE=22007

I did replace the date ( "2004-08-30") with DelDate


DelDate = ""

PARSE UPPER ARG delDate

do while (DelDate = "")
   say ""
   say "Missing the Del Date.  Please re-enter."
   say ""
   say "    Format: YYYY-MM-DD"
   say ""
   PARSE UPPER PULL DelDate
   Deldate = DelDate
   DelDate = left(deldate,4)||'-'||right(deldate,5)
end

0
 
chanitoCommented:
The program looks fine, are you using quotes to enter the date?
0
 
awehmeAuthor Commented:
no, should I .........single or double quotes ( ' or '' or " )

Thanks
0
 
chanitoCommented:
I don't think you should use quotes but can't hurt to try it.  I can't tell for sure from your examples the synthax you should use but, the line where you replace the date should be something like:

'a.ord_appv_deliv_dt='||DelDate||' and',

Also, it seems it's case senstivite, so even though the parameter parsing part is confusing I'd try it with delDate and deldate.
0
 
awehmeAuthor Commented:
Thanks canito,  putting the || around the arg worked.  Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now