2 Replies Latest reply: Sep 26, 2012 11:37 AM by AK C RSS

    Help need on Date Calculation, Date Changed, 30/04/2006 to 30/12/1899

      Dear All,

       

      I am struggling with wrong date value.

       

      Sceanrio

       

      I am loading some data from the dBase III+ dbf files with the help of user DSN.

      I want to add extra date type field to the table. For this I calculate a date with the help of under written statement

       


      ak=date#(Peek('vDat',0,'tmpDate'));

      enDate=monthend(MakeDate(year(ak),month(ak)))

      enDate=30/04/2006

       

      But as soon as I add this to the table date value changed to 30/12/1899 , here I am struggling what to to do I dont know.

      I tried some thing but I am failled. I am using under written script in my QVW

       

      ODBC CONNECT TO KLS;

       

      tmpDate:

      load *; SQL select max(VOU_DAT) as vDat from d:\Dos\Work\jour6 group by year(VOU_DAT),month(VOU_DAT);

       

      ak=date#(Peek('vDat',0,'tmpDate'));

      enDate=monthend(MakeDate(year(ak),month(ak)))

       

      trn:

                load *, $(enDate) as trDate          ;

                SQL

                select * from (

                          select trCod,sum(amt1) as trAmt from (

                                    select HED_COD as trCod, OP_BAL as amt1 from head6

                                    union all

                                    select HED_COD as trCod, sum(amt) as amt1 from jour6 where vou_Dat<=$(aks) group by HED_COD

                          ) as tb1 group by trCod

                ) as tb2 where trAmt<>0;

       

      Please Help, Thanks In Advance

       

      Regards

       

      IndianQvLover