CODEWITHSUNDEEP
asp.netsqloracle-databasedate
smilu
smilu

Reputation: 899

Difference between RRRR and YYYY in Oracle?

I have a stored procedure written in Oracle. I'm using a where condition in which i'm comparing two dates. I have a strange problem where the procedure works from oracle perfectly and when run from asp.net it just returns 0. Please look below for the procedure 

PROCEDURE SAMPLEPROC
(
    P_DATE IN VARCHAR2,
    P_COUNT OUT NUMBER
)
AS
BEGIN
SELECT count(*) INTO P_COUNT from dual where 
to_date(sysdate,'dd-mm-yyyy')=to_date(P_DATE,'dd-mm-yyyy');
END;

Now, when i run the above code with below input.

INPUT : 11-02-2014

OUTPUT FROM ORACLE  : COUNT=1
OUTPUT FROM ASP.NET : COUNT=0

Then, After wasting long time, My friend just changed the format of the date to dd-mm-rrrr as shown below

  SELECT count(*) INTO P_COUNT from dual where 
        to_date(sysdate,'dd-mm-rrrr')=to_date(P_DATE,'dd-mm-rrrr')

After making the changes when we run with the same Input as above

   INPUT : 11-02-2014

    OUTPUT FROM ORACLE  : COUNT=1
    OUTPUT FROM ASP.NET : COUNT=1

Why is it like this? Why is it behaving in this way when run from ASP.NET

Please someone give me a explanation for this?

Added: From ASP.NET my date will be always coming as 11-02-2014 ie DD-MM-YYYY format and its not a Date datatype. Its just a string. The same procedure works perfectly when running from ORACLE and when sent data from Asp.net it gives me 0. I debugged it I saw that the data coming from asp.net is perfectly the same which I pass from oracle when testing.

So, I just need an explanation why it could be like this when YYYY was replaced with RRRR when,

SELECT TO_DATE(SYSDATE,'DD-MM-YYYY') FROM DUAL;
AND
SELECT TO_DATE(SYSDATE,'DD-MM-RRRR') FROM DUAL;

returns the same data '11-02-2014'

Upvotes: 0

Views: 4210

Answers (1)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

  SELECT count(*) INTO P_COUNT from dual where 
       trunc( sysdate) =to_date(P_DATE,'dd-mm-yyyy')

It has to be like this. sysdate already returns a date.

I believe ur nls_date_format is different in your sqlplus and asp connection.

So, to_date(sydate) is attempt to work on some string.. which I guess is mm-dd-yyyy or dd-mm-rr(most probably) and so there could be mismatch.

SELECT value
FROM   nls_session_parameters
WHERE  parameter = 'NLS_DATE_FORMAT'

please run the above query in both places to catch the mismatch!

Upvotes: 1

Related Questions