and

the scripts which you write , return date with time as well by deafault , how is that made possible.

Hi Tom,

I have a procedure that fetchs data from a cursor in which one of its columns is a varchar2(9) datatype, which contains values in the format 'dd-mon-yy' or the word 'null'.

The default value for the parameter nls_date_format is 'dd-mon-rr'

If I set nls_date_format to 'mm/dd/yyyy' on a sql*plus session and then run the procedure, I get an error 6502 PL/SQL: numeric or value error, when the cursor tries to fetch values in the format 'dd-mon-yy'.

But If I leave the nls_date_format in its default value (dd-mon-rr) or if I set it to 'mm/dd/yy', the procedure runs without any problem on sql*plus for any kind of value ('dd-mon-yy' or the word null).

1-Can you tell me why I am getting this error if I change the nls_date_format to 'mm/dd/yyyy'?
2-Due to I have a mix of words and dates in a varchar2 datatype column, shouldn't I receive the error no matter what the setting is for the nls_Date_format parameter?

Thanks in advance for any help on this.

So i am just wondering what you believe is best practice for writing code in applications (apart from moving all the sql into the database!)??

If we have a java app using jdbc to oracle 8.1.7.4.

1) Then should any sql that references and compares dates in the app code, do an explicit TO_DATE on each date??

ie WHERE mydatefield = TO_DATE('01/01/2001', 'DD/MM/RRRR');

Obviously this makes a call to the TO_DATE function, so is this the most efficient way i.e. more efficient than forcing all jdbc clients to use a certain nls_date_format eg using a logon trigger or env variable and then coding eg:

WHERE mydatefield = '01/01/2001';

ie no call to TO_DATE. or is this so neglible that i shouldn't worry about it? I'm mainly thinking about some app which may run some sql code hundreds or thousands of times a day, so calling TO_DATE a lot!

2) If the client either odbc or jdbc connects to the database without any registry settings or environmental settings for nls_date_format, then which format would it pick up, the nls_instance_parameter settings??
What if NLS_LANG is set in the registry but not the nls_date_format??

3) How can i use sqlplus to emulate the jdbc/odbc clients. ie i want to test some sql using the same nls settings that the client would use. At the moment i issues an explicit allter session set nls_date_format in my glogin.sql script but if i want sqlplus to use the nls_instance_parameters what should i do, delete the glogin script??


Thanks for your help.

SQL>select to_char(sysdate,'day dddth month yyyy') from dual;

TO_CHAR(SYSDATE,'DAYDDDTHMONTH
------------------------------
thursday  118th april     2005

SQL>select to_char(sysdate,'day ddddth month yyyy') from dual;

TO_CHAR(SYSDATE,'DAYDDDDTHMONTH
-------------------------------
thursday  1185th april     2005

Having a good laugh after a bad day :-) 

Hi

Have always had problems with dates in my app since (rightly or wrongly) the app passes parameters from one form to another via globals! This give me a headache since dates are passed in this manner and thus become character strings giving problems especially when using DDMMYY. I get round this be using some sql to make sure I am getting the correct century. However I have noticed a peculiarity with the year 50 !

My code :-

/* forgot problem with using 6 digit dates
if key in 010150 routine comes back with 01012050
if key in 311249 routine comes back with 31121949
*/

BEGIN
/* will decode DDMMYY DDMMYYYY */
select
to_date(to_char(to_date(ON_SET,'ddmmrrrr'),'ddmmrrrr'),'ddmmrrrr')
into CHECK_DATE
from dual;
if check_date is not null then
if check_date > sysdate then
check_date := add_months(check_date,-100*12);
end if;
D_DATE := to_char(CHECK_DATE,'DD/MM/YYYY 00:00:00');
return;
end if;
exception when others then null;
END;
What is special for 50 to Oracle ?

Have also hit problems when using dates > year 2050 !

Any comments would be appreciated

Thanks

Hi

Thanks for your response

Just a follow up on my BUG !

Dates are entered into my via forms using ddmmrr after 2000
Prior to that were using ddmmyy
Transaction dates started around 1993/94

Date mask of dd/mm/yyyy is used for Dates of Birth

Only time hit a bit of hassle is when date is passed to a global or entered into a char field

Current example are when “dates” are entered into character fields when an exact date is not known – “date” of onset of clinical condition
i.e may-1994, 1955, 01/01/1994, 01011994, 01/01/93,12/94 etc which is more of a narrative than proper dates

Do you think sql for processing “global” dates in previous post handles dates ok from 1990 - future ?

Thanks

Does anyone know how to call a function from within SQL?? Hum? If you can call a "C" DLL function within an SQL program, Please let me now how! I might be able to offer a solution on all your mentioned date format problems.

Thom.

Tom,

I have a problem with related to dates conversions with a software package working on oracle 9.2.0.6.0 : after working fine for a while, some process start crashing all the time because of date conversions issues (there are implicit conversions somewhere in the code).
After some investigation, we found out that oracle seems not to resolve the right NLS_DATE_FORMAT parameter. I mean it uses the DATABASE level parameter instead of the SESSION level parameter set with an ALTER SESSION command :

Here is what i found :

SELECT * FROM NLS_DATABASE_PARAMETERS where parameter = 'NLS_DATE_FORMAT';
---------------------------------------------
PARAMETER VALUE
NLS_DATE_FORMAT DD-MON-RR

SELECT * FROM NLS_SESSION_PARAMETERS where parameter = 'NLS_DATE_FORMAT';
---------------------------------------------
PARAMETER VALUE
YYYYMMDD:HH24MISS 21-JUN-07


Select sysdate from dual;
---------------------------------------------
Sysdate
21-JUN-07

As you can see, the default date format defers from the session level NLS_DATE_FORMAT.

I saw on some website that there could be a bug for the format resolution on oracle 7xxxx, but nothing about it for later versions of oracle.

What do you think ?

Regards,
Olivier

Hi there,

I have this inserting query in Proc program :

INSERT INTO ORDER (DATEORDER)
VALUES (to_date(decode(:ecddenlev,'-1',null,:ecddenlev),'DD/MM/RR HH24:MI')).

And also, I have the variable :ecddenlev = '-1'.

When I launch the Proc program, I receive this error message :

ORA-01858: a non-numeric character was found where a numeric was expected.

Any Idea please.
Thanks in advance.

Best Regards
Yasser

Could be some issue wuth your data - please make SURE the bind variable ecddenlev REALLY is a string and it contains OR '-1', without ANY spaces or any extra characters, OR it contains a VALID data string in the given format... Example :

SYSTEM@O11GR2:SQL> create table ORDER (DATEORDER date);

Table created.

SYSTEM@O11GR2:SQL> variable ecddenlev varchar2(40);
SYSTEM@O11GR2:SQL> exec :ecddenlev := '-1';

PL/SQL procedure successfully completed.

SYSTEM@O11GR2:SQL> print ecddenlev

ECDDENLEV
--------------------------------------------------------------------------------------------------------------------------------
-1

==> Now I try your code :

SYSTEM@O11GR2:SQL> Begin
2 INSERT INTO ORDER (DATEORDER)
3 VALUES (to_date(decode(:ecddenlev,'-1',null,:ecddenlev),'DD/MM/RR HH24:MI'));
4 END;
5 /

SYSTEM@O11GR2:SQL> select * from ORDER;

DATEORDE
--------


1 linha selecionada.

=> ok, a record with NULL date was inserted, as expected... Now let´s put a valid date string using the given format in the variable :

SYSTEM@O11GR2:SQL> exec :ecddenlev := '01/01/20 13:14';

PL/SQL procedure successfully completed.

SYSTEM@O11GR2:SQL> print ecddenlev

ECDDENLEV
--------------------------------------------------------------------------------------------------------------------------------
01/01/20 13:14

SYSTEM@O11GR2:SQL> Begin
2 INSERT INTO ORDER (DATEORDER)
3 VALUES (to_date(decode(:ecddenlev,'-1',null,:ecddenlev),'DD/MM/RR HH24:MI'));
4 END;
5 /

PL/SQL procedure successfully completed.

SYSTEM@O11GR2:SQL> select * from ORDER;

DATEORDE
--------

01/01/20

2 rows selected.

=> Now I will put an extra space in the -1 string :

SYSTEM@O11GR2:SQL> exec :ecddenlev := '-1 ';

PL/SQL procedure successfully completed.

=> As expected, I got an conversion error because the DECODE tried to pass the string to the TO_DATE function :

SYSTEM@O11GR2:SQL> Begin
2 INSERT INTO ORDER (DATEORDER)
3 VALUES (to_date(decode(:ecddenlev,'-1',null,:ecddenlev),'DD/MM/RR HH24:MI'));
4 END;
5 /

BEGIN
*
ERROR at line 1:
ORA-01847: day of month must be between 1 and last day of month
ORA-06512: at line 2


SYSTEM@O11GR2:SQL>

=> So, my first react is : CONFIRM that in your proc the variable ecddenlev REALLY is a string AND it contains '-1', without ANY spaces or any extra characters, OR it contains a VALID data string in the given format...

Regards,

Chiappa

For an Example with the bind variable containing a date string in incorrect format, see :

SYSTEM@O11GR2:SQL> exec :ecddenlev := 'may/05/2020 14:00 ';

PL/SQL procedure successfully completed.

SYSTEM@O11GR2:SQL> print ecddenlev

ECDDENLEV
--------------------------------------------------------------------------------------------------------------------------------
may/05/2020 14:00

SYSTEM@O11GR2:SQL> ed
Wrote file afiedt.buf

1 Begin
2 INSERT INTO ORDERS (DATEORDER)
3 VALUES (to_date(decode(:ecddenlev,'-1',null,:ecddenlev),'DD/MM/RR HH24:MI'));
4* END;
SYSTEM@O11GR2:SQL> /
Begin
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 2


SYSTEM@O11GR2:SQL>

We got the same error message reported by you, okdoc ?? Maybe this is your issue....

Regards,

Chiappa