Home » Developer & Programmer » Forms » Date Issue (Forms 6i, Oracle 10g)
Date Issue [message #682249] Tue, 13 October 2020 04:52 Go to next message
chat2raj.s
Messages: 140
Registered: October 2010
Location: Chennai, India
Senior Member
Dear Sir,

I am reading a date column from Table A into a date variable and updating it in a date column in Table B. System found this date smaller than today's date and so to check i made a to_char of this updated date column and i get like 13-Oct-0020 instead of 13-Oct-2020.

The nls_date_format is dd.mm.yy and the problem is only in the above update, rest it works well as is.

Thank you
Re: Date Issue [message #682257 is a reply to message #682249] Tue, 13 October 2020 15:04 Go to previous messageGo to next message
Littlefoot
Messages: 21623
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You never said how exactly you did that, but - mentioning TO_CHAR function, it looks like you forced Oracle to implicitly convert datatypes and - sadly - provided insufficient info and it (Oracle) failed.

If the target column's datatype is DATE, then leave strings (which is what TO_CHAR does - converts "something" to a string) and work with dates. For example:
update table_b set date_column = trunc(sysdate);

or

update table_b set date_column = date '2020-10-13';

or

update table_b set date_column = to_date('13.10.2020', 'dd.mm.yyyy');
All those examples use DATE datatype, and so should you.
Re: Date Issue [message #682265 is a reply to message #682257] Wed, 14 October 2020 02:06 Go to previous messageGo to next message
chat2raj.s
Messages: 140
Registered: October 2010
Location: Chennai, India
Senior Member
Below procedure is used to fetch the date from source table directly to the form (date field) using the OUT variable.
I tried to use to_char, just to read/know the complete date format stored in the destination field and found the year as 0020 instead of 2020 as expected.

CREATE OR REPLACE PROCEDURE CTC_FETCH_BOL_DT_NO_FROM_SHADV (P_REF_SYS_ID NUMBER, M_BOL_NO OUT VARCHAR2, M_BOL_DT OUT DATE) AS

  CURSOR C1 IS
  SELECT SH_BL_AWB, SH_ETS_DT
    FROM OT_SHIP_HEAD, OT_GR_HEAD
   WHERE GH_REF_SYS_ID   = SH_SYS_ID
     AND GH_SYS_ID = P_REF_SYS_ID;

BEGIN  
  IF C1%ISOPEN THEN
     CLOSE C1;
  END IF;
  OPEN C1;
  FETCH C1 INTO M_BOL_NO, M_BOL_DT;
  IF C1%NOTFOUND THEN
     CLOSE C1;
     RAISE_APPLICATION('OP',220131,'Bill of Lading date not found for this GRN','','','','','','','');
  END IF;
  CLOSE C1;
END;
Re: Date Issue [message #682267 is a reply to message #682265] Wed, 14 October 2020 02:23 Go to previous messageGo to next message
Michel Cadot
Messages: 67540
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I tried to use to_char, just to read/know the complete date format stored in the destination field and found the year as 0020 instead of 2020 as expected.
Why do you use TO_CHAR if both columns and variables are of type DATE?

Re: Date Issue [message #682268 is a reply to message #682267] Wed, 14 October 2020 02:46 Go to previous messageGo to next message
chat2raj.s
Messages: 140
Registered: October 2010
Location: Chennai, India
Senior Member
That was not used to update the date anywhere. Just to know the format of how the date is stored, infact after using tochar i found the date is actually in year 0020 instead of 2020.
Re: Date Issue [message #682270 is a reply to message #682268] Wed, 14 October 2020 04:08 Go to previous messageGo to next message
Michel Cadot
Messages: 67540
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is because an error was made when the dates have been enter.
They have been inserted with a wrong mask format like 'YY' instead of 'YYYY'.
Now the data are wrong inside the database, you have to update all dates.
For instance, add 2000 to all those that are less than 100 (but first check you can't have a date in 19xx).

Re: Date Issue [message #682271 is a reply to message #682270] Wed, 14 October 2020 04:34 Go to previous messageGo to next message
chat2raj.s
Messages: 140
Registered: October 2010
Location: Chennai, India
Senior Member
I checked the source table dates and found them to be in YY format and applied to_char on it to see correct format as YYYY and it is 2020 only.

SQL> SELECT sh_ets_dt,To_Char(SH_ETS_DT, 'dd.mon.yyyy') FROM ot_ship_head WHERE sh_txn_code = '5106SHAD' AND sh_no = 358;
SH_ETS_DT TO_CHAR(SH_                                                           
--------- -----------                                                           
02-AUG-20 02.aug.2020  
Re: Date Issue [message #682272 is a reply to message #682271] Wed, 14 October 2020 05:27 Go to previous messageGo to next message
John Watson
Messages: 8424
Registered: January 2010
Location: Global Village
Senior Member
Quote:
I checked the source table dates and found them to be in YY format
No! dates are stored as dates. Oracle encodes them into a 7 byte format that us normal human beings NEVER see. What you are seeing is dates converted to strings, using whatever the session happens to have for its NLS_DATE_FORMAT variable. In your, case it is dd-MON-yy. I always use yyyy-mm-dd:hh24:mi:ss, so I see this:
orclz> select hiredate from emp where rownum=1;

HIREDATE
-------------------
1980-12-17:00:00:00

orclz>
Re: Date Issue [message #682276 is a reply to message #682272] Thu, 15 October 2020 03:46 Go to previous messageGo to next message
chat2raj.s
Messages: 140
Registered: October 2010
Location: Chennai, India
Senior Member
Just to show you the exact case simulated yesterday as below.
I am reading SH_ETS_DT in OUT variable to the form field INVH_BOL_DT through the procedure mentioned earlier in this post.

SQL> SELECT SH_ETS_DT, To_Char(SH_ETS_DT, 'dd.mm.yyyy')
  2    FROM OT_SHIP_HEAD, OT_GR_HEAD
  3   WHERE GH_REF_SYS_ID   = SH_SYS_ID
  4     AND GH_SYS_ID = 18982377;

SH_ETS_DT TO_CHAR(SH                                                            
--------- ----------                                                            
22-SEP-20 22.09.2020                                                            

SQL> SELECT invh_bol_dt, To_Char(invh_bol_dt, 'dd.mm.yyyy'), invh_ref_sys_id
  2    FROM ot_invoice_head
  3   WHERE invh_txn_code = '5105BUSIDN'
  4     AND Trunc(invh_cr_dt) >= Trunc(SYSDATE);

INVH_BOL_ TO_CHAR(IN INVH_REF_SYS_ID                                            
--------- ---------- ---------------                                            
22-SEP-20 22.09.0020        18982377 
Re: Date Issue [message #682304 is a reply to message #682276] Thu, 15 October 2020 10:21 Go to previous messageGo to next message
Michel Cadot
Messages: 67540
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is what I said, the dates where wrongly inserted and so you have to update them.

Re: Date Issue [message #682331 is a reply to message #682304] Fri, 16 October 2020 10:09 Go to previous message
cookiemonster
Messages: 13895
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'd advise changing your sessions nls_date_format (you can create a glogin.sql that'll run every time you start sqlplus to do this)
alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';

then you can select dates and see exactly what they are set to without bothering with to_char.

So SH_ETS_DT is right and invh_bol_dt is wrong.
Is invh_bol_dt the one being updated?
If so then there's a problem with either the update or the variables being used to hold the value until you do the update.
Since you're still not sharing the full code it's impossible to tell.

Side note - locally declared cursors are never open at the start of a procedure - they go out of scope every time the procedure finishes, so that initial IF in your procedure is pointless.
Previous Topic: Urdu character shows but in wrong sequence as i type it
Next Topic: how to call chrome browser using OLE2 or DDE in Oracle forms 6i ?
Goto Forum:
  


Current Time: Wed Nov 25 07:09:37 CST 2020