Date Issue [message #682249] |
Tue, 13 October 2020 04:52 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
chat2raj.s
Messages: 161 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 #682265 is a reply to message #682257] |
Wed, 14 October 2020 02:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
chat2raj.s
Messages: 161 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 #682268 is a reply to message #682267] |
Wed, 14 October 2020 02:46 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
chat2raj.s
Messages: 161 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 #682271 is a reply to message #682270] |
Wed, 14 October 2020 04:34 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
chat2raj.s
Messages: 161 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
chat2raj.s
Messages: 161 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 #682331 is a reply to message #682304] |
Fri, 16 October 2020 10:09 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
cookiemonster
Messages: 13937 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.
|
|
|