CODEWITHSUNDEEP
oracle-databasedatetimestored-procedures
b0x0rz
b0x0rz

Reputation: 3981

Inserting time section in date field using oracle

I have a procedure in oracle:

CREATE OR REPLACE 
PROCEDURE ONE
    (
    p_GUID IN ONE.GUID%TYPE,
    p_START_DATE IN ONE.START_DATE%TYPE,
    p_END_DATE IN ONE.END_DATE%TYPE,
    p_RETURN OUT INTEGER
    )
AS
BEGIN
    INSERT INTO ONETABLE (GUID, START_DATE, END_DATE)
  VALUES (p_GUID, to_date(p_START_DATE, 'YYYY-MM-DD HH24:MI:SS'), to_date(p_END_DATE, 'YYYY-MM-DD HH24:MI:SS'));
    COMMIT;
  EXCEPTION
        WHEN OTHERS THEN
        p_RETURN:= 1;
END;

edit, because people seem to saying to try this (this does not work either, i tried to use to_date (above example) to try and fix the problem):

CREATE OR REPLACE 
PROCEDURE ONE
    (
    p_GUID IN ONE.GUID%TYPE,
    p_START_DATE IN ONE.START_DATE%TYPE,
    p_END_DATE IN ONE.END_DATE%TYPE,
    p_RETURN OUT INTEGER
    )
AS
BEGIN
    INSERT INTO ONETABLE (GUID, START_DATE, END_DATE)
  VALUES (p_GUID, p_START_DATE, p_END_DATE);
    COMMIT;
  EXCEPTION
        WHEN OTHERS THEN
        p_RETURN:= 1;
END;

When call it from c# (with DateTime.NOW for example) - it fails.

It only seems to work if the start date and end date are in the format of 05-apr-2013. but how do I get the time in there as well as in the input?

If I replace

p_START_DATE, 'YYYY-MM-DD HH24:MI:SS'

With

'2013-05-13 12:13:14', 'YYYY-MM-DD HH24:MI:SS'

This works, but upon calling the procedure from c# it fails.

Also If I manually run the procedure from db client I still have to input 05-apr-2013 and only that works. I can not input the time as it fails if I try so.

Start date and end date are DATE type in oracle db.

--

Depending what I try I get different messages.

As I investigated, it seems to have something to do with this:

SELECT value FROM v$nls_parameters WHERE parameter ='NLS_DATE_FORMAT';

Which returns:

DD-MON-RR

And I cannot change that as am not the db owner.

--

so how to get the time part in there?

Here is the complete SQL:

select parameter, value from nls_session_parameters;

Which returns:

NLS_LANGUAGE    AMERICAN
NLS_TERRITORY   AMERICA
NLS_CURRENCY    $
NLS_ISO_CURRENCY    AMERICA
NLS_NUMERIC_CHARACTERS  . 
NLS_CALENDAR    GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE   AMERICAN
NLS_SORT    BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT    DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT  HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY   $
NLS_COMP    BINARY
NLS_LENGTH_SEMANTICS    BYTE
NLS_NCHAR_CONV_EXCP FALSE

Upvotes: 1

Views: 3246

Answers (2)

ThinkJet
ThinkJet

Reputation: 6735

There are no need to convert function to DATE type. It's already date. Just insert value passed in parameter:

    INSERT INTO ONETABLE (GUID, START_DATE, END_DATE)
  VALUES (p_GUID, p_START_DATE, p_END_DATE);

Update

But if variant above don't work and you see raised error, stored procedure parameters definition must be checked on clients side. Can you verify that p_START_DATE parameter on client side have DateTime type?

Update 2

There are error on client side. Try to initialize date parameters like in example below:

OracleParameter p1 = new OracleParameter();
p1.DbType = DbType.DateTime;
p1.Value = System.DateTime.Now;

Reason of error probably is C# component treats parameter as string and convert it to string with default OS format, then passes it to Oracle with query. Oracle trying to parse that string to date type as specified in definition of ONE stored procedure and fails with that because expects default format from NLS_DATE_FORMAT parameters.

Update 3

Real cause of error explained by @b0x0rz in comments (wrong return type). Anyway it's client-side mistake...

Upvotes: 1

DazzaL
DazzaL

Reputation: 21973

as your column is of type DATE in oracle, your insert is wrong.

you should NOT do

to_date(p_START_DATE, 'YYYY-MM-DD HH24:MI:SS')

instead just insert

INSERT INTO ONETABLE (GUID, START_DATE, END_DATE)
  VALUES (p_GUID, p_START_DATE, p_END_DATE);

by doing to_Date on a date, you're forcing conversion from date to char and back to date again. in other words, your string:

to_date(p_START_DATE, 'YYYY-MM-DD HH24:MI:SS')

is silently converted to

to_date(to_char(p_START_DATE, NLS_DATE_FORMAT), 'YYYY-MM-DD HH24:MI:SS')

ie in your case, probably:

to_date(to_char(p_START_DATE, 'DD-MON-RR'), 'YYYY-MM-DD HH24:MI:SS')

as your function inputs are %TYPE (which is good), they are also dates:

p_START_DATE IN ONE.START_DATE%TYPE

so be sure, when you pass the input into the function that you're passing in a date type and not a string.

example:

SQL> CREATE OR REPLACE  PROCEDURE ONE
  2      (
  3      p_GUID IN ONETABLE.GUID%TYPE,
  4      p_START_DATE IN ONETABLE.START_DATE%TYPE,
  5      p_END_DATE IN ONETABLE.END_DATE%TYPE,
  6      p_RETURN OUT INTEGER
  7      )
  8  AS
  9  BEGIN
 10      INSERT INTO ONETABLE (GUID, START_DATE, END_DATE)
 11    VALUES (p_GUID, p_START_DATE, p_END_DATE);
 12      COMMIT;
 13    EXCEPTION
 14          WHEN OTHERS THEN
 15          p_RETURN:= 1;
 16  END;
 17  /

Procedure created.

SQL> var r number
SQL> exec ONE('abc', to_date('2013-05-13 12:13:14', 'YYYY-MM-DD HH24:MI:SS'),  to_date('2013-05-14 01:13:14', 'YYYY-MM-DD HH24:MI:SS'), :r);

PL/SQL procedure successfully completed.

SQL> select * from onetable;

GUID                 START_DATE         END_DATE
-------------------- ------------------ ------------------
abc                  13-may-13 12:13:14 14-may-13 01:13:14
abc                  13-may-13 12:13:14 14-may-13 01:13:14

vs you're implicit conversions:

SQL> CREATE OR REPLACE  PROCEDURE ONE
  2      (
  3      p_GUID IN ONETABLE.GUID%TYPE,
  4      p_START_DATE IN ONETABLE.START_DATE%TYPE,
  5      p_END_DATE IN ONETABLE.END_DATE%TYPE,
  6      p_RETURN OUT INTEGER
  7      )
  8  AS
  9  BEGIN
 10      INSERT INTO ONETABLE (GUID, START_DATE, END_DATE)
 11    VALUES (p_GUID, to_date(p_START_DATE, 'YYYY-MM-DD HH24:MI:SS'), to_date(p_END_DATE, 'YYYY-MM-DD HH24:MI:SS'));
 12      COMMIT;
 13
 14  END;
 15  /

Procedure created.

SQL> alter session set nls_date_format='dd-mon-yy';

Session altered.

SQL> var r number
SQL> exec ONE('abc', to_date('2013-05-13 12:13:14', 'YYYY-MM-DD HH24:MI:SS'),  to_date('2013-05-14 01:13:14', 'YYYY-MM-DD HH24:MI:SS'), :r);

PL/SQL procedure successfully completed.

SQL> alter session set nls_date_format='dd-mon-rrrr hh24:mi:ss';

Session altered.

SQL> select * from onetable;

GUID                 START_DATE           END_DATE
-------------------- -------------------- --------------------
abc                  13-may-0013 00:00:00 13-may-0014 00:00:00

wrong dates were stored..or with a diff NLS setting:

SQL> alter session set nls_date_format='dd-mon';

Session altered.

SQL> var r number
SQL> exec ONE('abc', to_date('2013-05-13 12:13:14', 'YYYY-MM-DD HH24:MI:SS'),  to_date('2013-05-14 01:13:14', 'YYYY-MM-DD HH24:MI:SS'), :r);
BEGIN ONE('abc', to_date('2013-05-13 12:13:14', 'YYYY-MM-DD HH24:MI:SS'),  to_date('2013-05-14 01:13:14', 'YYYY-MM-DD HH24:MI:SS'), :r); END;

*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "TEST.ONE", line 10
ORA-06512: at line 1

Upvotes: 2

Related Questions