Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005
    Posts
    8

    Exclamation Unanswered: Date problem: months and days interchanging

    Hi all

    I am having a problem with existing VB software backend Oracle 8.0.5. From past three month it is giving me lots of pain . By just changing day to month and vise-virsa
    for first 12 days this happen then after every thin is fine.

    Like todays is 02-jan-2006 and now it is reflecting in database as 01-feb-2006


    We are giving our data to a data-entry concern and then after we are uploading the text file. In text files date is coming properly but when we run process of uploading and processing the data. Date is enterchanged...................................... ...


    Plz help me ........................


    Sanjay

    eksanjay@gmail.com

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You said that you are "uploading the text file". If that means that you receive text file and use, for example, SQL*Loader to insert data into the Oracle database, change the way you are doing this. Do NOT leave Oracle guess what means "01/02/2006", but insert it using appropriate functions. For example, "TO_DATE('01/02/2006', 'mm/dd/yyyy')".

  3. #3
    Join Date
    Dec 2005
    Posts
    8

    Question plz go throu the code

    this the all code which is being used for uploading. the problem arises when i upload the file via window 98,2000 adv server or xp but if i upload the file from NT then it shows every thin fine.. the problem is totally known to me..
    Please help me out

    procedure Data_transfer
    (
    subno tmp_subscribermast.subsno%type ,
    uid tmp_subscribermast.userid%type,
    newsubno in out varchar2
    )
    as
    sno varchar2(11);

    begin



    select nvl(max(subsno),0) into sno from subscribermast where substr(subsno,1,2)=to_char(sysdate,'mm') and length(ltrim(rtrim(subsno)))=11 ;


    if sno = '0' then
    select to_char(sysdate,'mmyy')||lpad( '1',7,'0') into NEWSUBNO from dual;


    insert into subscribermast (
    SUBSNO, SALUTATION, FNAME, MNAME, DOB, SEX,
    DESIGNATION, DEPARTMENT, CompanyName, USERID,
    LASTUPDATDATE, LNAME, DEFUNCT, BLACKLIST, OFFICIAl)
    Select NEWSUBNO, SALUTATION, FNAME, MNAME, DOB, SEX,
    DESIGNATION, DEPARTMENT, CompanyName, Uid,
    sysdate, LNAME, DEFUNCT, BLACKLIST, OFFICIAL
    from
    tmp_subscribermast
    where
    tmp_subscribermast.subsno= subno;


    else

    select to_number(substr((sno),5,7))+1 into sno from dual;
    select to_char(sysdate,'mmyy')||lpad(SNO,7,'0') into sno from dual;



    insert into subscribermast (
    SUBSNO, SALUTATION, FNAME, MNAME, DOB, SEX,
    DESIGNATION, DEPARTMENT, CompanyName, USERID,
    LASTUPDATDATE, LNAME, DEFUNCT, BLACKLIST, OFFICIAl)
    select sno, SALUTATION, FNAME, MNAME, DOB, SEX,
    DESIGNATION, DEPARTMENT, CompanyName, Uid,
    sysdate, LNAME, DEFUNCT, BLACKLIST, OFFICIAL
    from
    tmp_subscribermast
    where
    tmp_subscribermast.subsno= subno;

    newsubno :=sno;

    end if;


    end data_transfer;
    ==============================

    procedure data_transfer_order
    (
    p_SUBSNO Subsordermast.subsno%type,
    p_ADDRESSID subsordermast.ADDRESSID%type,
    p_BULKORDER subsordermast.bulkorder%type,
    p_FOREIGNSUBS subsordermast.FOREIGNSUBS%type,
    p_AGENCYID subsordermast.AGENCYID%type,
    p_USERID subsordermast.USERID%type,
    p_LASTUPDATDATE subsordermast.LASTUPDATDATE%type,
    p_ORDERDATE subsordermast.ORDERDATE%type,
    p_SUBSSTATUSID subsordermast.SUBSSTATUSID%type,
    p_subsmodeid subsordermast.subsmodeid%type,
    p_TOREFUND subsordermast.TOREFUND%type,
    p_AMTTOPAY subsordermast.AMTTOPAY%type,
    p_AMTPAID subsordermast.AMTPAID%type,
    p_OUTSTANDINGAMT subsordermast.OUTSTANDINGAMT%type,
    p_DUEAMT subsordermast.DUEAMT%type,
    p_PRIORITY subsordermast.PRIORITY%type,
    p_FSAGENCYID subsordermast.FSAGENCYID%type,
    p_MERGED subsordermast.MERGED%type,
    p_CANCELLED subsordermast.CANCELLED%type,
    p_SUSPENDFULL subsordermast.SUSPENDFULL%type,
    p_SUSPENDPARTIAL subsordermast.SUSPENDPARTIAL%type,
    p_INQUIRYNO subsordermast.INQUIRYNO%type,
    p_LESSPAID subsordermast.LESSPAID%type,
    p_NEWORDERNO OUT SUBSORDERMAST.ORDERno%TYPE
    )
    as
    ono subsordermast.orderno%type;
    begin
    select max(orderno) into ono from subsordermast;
    if ono is null then
    ono :=1;
    else
    ono := ono+1;
    end if;
    P_NEWORDERNO := ono;
    insert into subsordermast
    (
    SUBsNO , ORDERNO , ADDRESSID, BULKORDER , FOREIGNSUBS, AGENCYID , USERID ,
    LASTUPDATDATE, ORDERDATE, SUBSSTATUSID , SUBSMODEID , TOREFUND ,
    INQUIRYNO , AMTTOPAY,AMTPAID ,OUTSTANDINGAMT , DUEAMT ,PRIORITY , FSAGENCYID, MERGED
    , CANCELLED , SUSPENDFULL ,
    SUSPENDPARTIAL , LESSPAID
    )
    values(
    p_sUBsNO , Ono , p_ADDRESSID, p_BULKORDER , p_FOREIGNSUBS, p_AGENCYID , p_USERID ,
    sysdate, p_ORDERDATE , p_SUBSSTATUSID, p_SUBSMODEID ,p_TOREFUND , p_INQUIRYNO ,
    p_AMTTOPAY , p_AMTPAID , p_OUTSTANDINGAMT , p_DUEAMT ,p_PRIORITY, p_FSAGENCYID , p_MERGED,
    p_CANCELLED, p_SUSPENDFULL , p_SUSPENDPARTIAL , p_LESSPAID
    );
    end data_transfer_order;

    ==============================


    Procedure data_transfer_address

    (
    subno tmp_subscribermast.subsno%type ,
    uid tmp_subscribermast.userid%type,
    newsubno in subscribermast.subsno%type,
    OLD_addid in subsaddmast.addressid%type
    )
    as
    addno number(11);
    begin
    select max(addressid) into addno from subsaddmast;
    if addno is null then
    addno := 1;
    else
    addno :=addno+1;
    end if;
    insert into subsaddmast
    ( SUBSNO , ADDRESSID,SALUTATION,FNAME, MNAME,LNAME , ADD1,ADD2, ADD3 ,
    CITYID,PINCODE,USERID,EMAILID ,CONTACTNO1 ,CONTACTNO2,FAXNO,LASTUPDATEDATE,
    SUPPORTINGDOCUNO,AUTHORIZEDBY, EXTNO,depart,desig,compname )
    select
    newSUBNO , addno,SALUTATION,FNAME, MNAME,LNAME , ADD1,ADD2, ADD3 ,
    CITYID,PINCODE,USERID,EMAILID ,CONTACTNO1 ,CONTACTNO2,FAXNO,sysDATE,
    SUPPORTINGDOCUNO,AUTHORIZEDBY, EXTNO,depart,desig,compname
    from tmp_subsaddmast where subsno=subno and addressid=OLD_addid;
    UPDATE SUBSORDERMAST SET ADDRESSID=ADDNO WHERE ADDRESSID=OLD_ADDID;
    UPDATE ORDERPUBSHIPADD SET ADDRESSID=ADDNO WHERE ADDRESSID=OLD_ADDID;
    UPDATE ORDERPUBGIFTADD SET ADDRESSID=ADDNO WHERE ADDRESSID=OLD_ADDID;
    end data_transfer_address;

    ------------------------------------------

    procedure data_transfer_payment
    (
    oldOrderNo subsordermast.orderno%type ,
    NewOrderNo subsordermast.orderno%type ,
    OldPayId Orderpaymentdetail.paymentid%type,
    uid tmp_subscribermast.userid%type
    )
    as
    NewPayId orderpaymentdetail.paymentid%type;
    begin
    select max(paymentid) into newpayid from orderpaymentdetail;
    if newpayid is null then
    newpayid := 1;
    else
    newpayid :=newpayid+1;
    end if;
    insert into orderpaymentdetail
    (
    ORDERNO,PAYMENTID,PAYMENTTYPEID,PAYMENTAMT,CURRENC YID,INSTRUMENTNO,INSTRUMENTDATE,
    BANKNAME,BANKBRANCH,CITYID,OUTSTATIONCHARGES,CCEXP IRYDATE,CCHOLDERDOB,CCAUTHORIZATIONNO,
    RECEIPTDATE,STATUS,USERID,LASTUPDATEDATE,OUTCHARGE PENDING,paymentdate)
    select newORDERNO,newPAYID,PAYMENTTYPEID,PAYMENTAMT,CURRE NCYID,INSTRUMENTNO,INSTRUMENTDATE,
    BANKNAME,BANKBRANCH,CITYID,OUTSTATIONCHARGES,CCEXP IRYDATE,CCHOLDERDOB,CCAUTHORIZATIONNO ,
    RECEIPTDATE,STATUS,USERID,LASTUPDATEDATE,OUTCHARGE PENDING,paymentdate
    from tmp_orderpaymentdetail where orderno=oldorderno and paymentid=oldpayid;

    end data_transfer_payment;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •