Board index » delphi » Oracle 7.3 feb 29 2000 problems

Oracle 7.3 feb 29 2000 problems

I am running Oracle 7.3.2.1.1 on NT 4 sp3 and using Delphi 4 c/s update pack
3

I have the following table defined

prl_premium
BRANCH VARCHAR2(2)
POLICY VARCHAR2(6)
BUSINESS VARCHAR2(2)
FORM VARCHAR2(14)
RECEIPT_DATE DATE
UPR VARCHAR2(16)
TYPE VARCHAR2(16)
LIRMA VARCHAR2(6)
...

with an unique index defined on fields
BRANCH,POLICY,BUSINESS,FORM,RECEIPT_DATE

I am having the following problems.
I have an sql (called sqlPREMIUM) that contains the follow
SELECT * FROM PRL_PREMIUM WHERE BRANCH = '08' AND POLICY = '030501' AND
BUSINESS = '08' and FORM = 'A/321 /1999'.
I am using DateTimePicker1 to allow the user to enter a receipt date. When
DateTimePicker1.Date is set to 29/02/2000 and I issue the following command
sqlPREMIUMRECEIPT_DATE.AsDateTime := DateTimePicker1.Date I get ORA-01839:
date not valid for month specified error message. This is not too serious I
can do work arounds (sql UPDATE commands)
I cannot use a data-aware component here unfortunately.

However, when I am try to issue the following sql command
SELECT * FROM PRL_PREMIUM WHERE RECEIPT_DATE = '29-FEB-00' but I am getting
"ORA-01839: date not valid for month specified" error message.
This i cannot work around, and I cannot use the TO_DATE function i.e. SELECT
* FROM PRL_PREMIUM WHERE RECEIPT_DATE = TO_DATE('29-02-2000','DD/MM/YYYY')
as the TO_DATE gives me "capability not supported" error message.

I have checked the oracle web site and finally found a reference to control
parameter nls_date_format. The default is dd-mon-yy, You can issue the
following sql command
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR'
then SELECT * FROM PRL_PREMIUM WHERE RECEIPT_DATE = '29-FEB-00' works, as I
tried it in Oracle SQL Worksheet.

How do you issue the alter session command in Delphi. I tried putting it
into an TQuery, but I get the following message
Invalid use of Keyword.
Token SESSION
Line number 1

How can get delphi to run the alter session command if possible? If it is
possible, will it work while my delphi app is running.

Thanks in advance

Regards

Darren Guy

please remove all references to nospam in my email address if replying
personally
/................................................................\
Darren Guy
Colonial Software Limited
http://www.colonial.demon.co.uk
\................................................................/

 

Re:Oracle 7.3 feb 29 2000 problems


I have found an article that says there is a registry command that you can
change

HKEY_LOCAL_MACHINE\Software\Oracle\NLS_DATE_FORMAT

but with this client I am reluctant to modify their registry. Is there a way
of calling
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR' in code

Quote
>Darren Guy

>please remove all references to nospam in my email address if replying
>personally
>/................................................................\
>Darren Guy
>Colonial Software Limited
>http://www.colonial.demon.co.uk
>\................................................................/

Re:Oracle 7.3 feb 29 2000 problems


The date you are giving Oracle is in 1900.  The 2-digit year defaults to
the current century unless you change nls_date_format.  29-FEB-2OOO
should work (I can't test because I've changed my date format to
mm/dd/yyyy).  Using the TO_DATE function works when you use a query and
request live is false.  Then the BDE uses
passthrough SQL.

good luck

John

Re:Oracle 7.3 feb 29 2000 problems


I' m  right about 1900 but I'm wrong about it accepting '29-feb-2000'.
You can use ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY' or
anything else for that matter.  When I change the date format I change
it on the server and the client.  I haven't checked what the result of
the session command is on the BDE.

John

Re:Oracle 7.3 feb 29 2000 problems


Hi,
The ALTER SESSION works for me in a TQuery. You should only make sure that
the statement is sent directly to Oracle (PASSTHROUGH) as opposed to being
interpreted by BDE.

HTH
Florin

Quote
Darren Guy wrote in message <7jjkul$41...@forums.borland.com>...
> .......
>How do you issue the alter session command in Delphi. I tried putting it
>into an TQuery, but I get the following message
>Invalid use of Keyword.
>Token SESSION
>Line number 1

>How can get delphi to run the alter session command if possible? If it is
>possible, will it work while my delphi app is running.

Other Threads