| Create a oracle function that parse a string to date time when not null [message #689253] |
Mon, 30 October 2023 04:54  |
 |
DorababuMeka
Messages: 11
Registered: October 2023
|
Junior Member |
|
|
I am trying to create a function for the following query which is working as inline query but not as a function
select TO_CHAR(TO_DATE('2-23-1999','mm-dd-yyyy'),
'DD/MM/YYYY') from dual;
CREATE OR REPLACE FUNCTION my_to_date(p_str IN VARCHAR2)
RETURN DATE
IS
v_date DATE;`
BEGIN
v_date := TO_CHAR(TO_DATE(p_str,'mm-dd-yyyy'),
'DD/MM/YYYY');
RETURN v_date;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL; -- or handle the specific exception as needed
END;
[Updated on: Mon, 30 October 2023 04:59] Report message to a moderator |
|
|
|
| Re: Create a oracle function that parse a string to date time when not null [message #689254 is a reply to message #689253] |
Mon, 30 October 2023 05:17  |
 |
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
1/ Your function is not equivalent to the query as your query returns a string and your function a date.
2/ As v_date is declared as a DATE and the expression "TO_CHAR(TO_DATE(p_str,'mm-dd-yyyy'),'DD/MM/YYYY')" is a string, you bave an implicit conversion which may or not generate an exception depending on client environment.
3/ "WHEN OTHERS" don't do that, read WHEN OTHERS
4/ You have an extra "`" at the end of line 4
Proof of 2/:
SQL> CREATE OR REPLACE FUNCTION my_to_date(p_str IN VARCHAR2)
2 RETURN DATE
3 IS
4 v_date DATE;
5 BEGIN
6 v_date := TO_CHAR(TO_DATE(p_str,'mm-dd-yyyy'),
7 'DD/MM/YYYY');
8 RETURN v_date;
9 END;
10 /
Function created.
SQL> select my_to_date('10-30-2023') from dual;
MY_TO_DATE('10-30-2
-------------------
30/10/2023 00:00:00
1 row selected.
SQL> alter session set nls_date_format='YYYY/MM/DD';
Session altered.
SQL> select my_to_date('10-30-2023') from dual;
select my_to_date('10-30-2023') from dual
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at "MICHEL.MY_TO_DATE", line 6
If you want (from your function name) a function which validates the input string format and returns the corresponding date then just use:
SQL> CREATE OR REPLACE FUNCTION my_to_date(p_str IN VARCHAR2)
2 RETURN DATE
3 IS
4 BEGIN
5 RETURN TO_DATE (p_str, 'mm-dd-yyyy');
6 END;
7 /
Function created.
SQL> select my_to_date('10-30-2023') from dual;
MY_TO_DATE
----------
2023/10/30
1 row selected.
SQL> select my_to_date('30/10/2023') from dual;
select my_to_date('30/10/2023') from dual
*
ERROR at line 1:
ORA-01843: not a valid month
ORA-06512: at "MICHEL.MY_TO_DATE", line 5
|
|
|
|
|
|
|
|
| Re: Create a oracle function that parse a string to date time when not null [message #689257 is a reply to message #689256] |
Mon, 30 October 2023 05:44 |
|
DorababuMeka
Messages: 11
Registered: October 2023
|
Junior Member |
|
|
I have updated as follows but still I am confused with normal execution and function execution
CREATE OR REPLACE FUNCTION my_to_date(p_str IN VARCHAR2)
RETURN DATE
IS
v_date DATE;
BEGIN
v_date := TO_CHAR(TO_DATE(p_str,'mm-dd-yyyy'),
'MM/DD/YYYY');
RETURN v_date;
EXCEPTION
WHEN OTHERS THEN
RAISE; -- or handle the specific exception as needed
END;
SELECT my_to_date('02-23-2023') AS result_date FROM dual;
SELECT TO_CHAR(TO_DATE('2-23-2023','mm-dd-yyyy'),
'MM/DD/YYYY') FROM DUAL;[Updated on: Mon, 30 October 2023 05:46] Report message to a moderator |
|
|
|
| Re: Create a oracle function that parse a string to date time when not null [message #689258 is a reply to message #689257] |
Mon, 30 October 2023 06:34 |
John Watson
Messages: 8977
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
This, v_date := TO_CHAR(TO_DATE(p_str,'mm-dd-yyyy'),
'MM/DD/YYYY');
You can't do that. So Oracle has to do implicit type casting: it attempts to convert the string to a date, and assign that. When it does the implicit type casting, it relies on your session's default nls_date_format.
The results are dependent on that setting and are erratic. For example, for me:
orclz>
orclz> SELECT my_to_date('23-02-2023') AS result_date FROM dual;
SELECT my_to_date('23-02-2023') AS result_date FROM dual
*
ERROR at line 1:
ORA-01843: not a valid month
ORA-06512: at "SCOTT.MY_TO_DATE", line 11
ORA-06512: at "SCOTT.MY_TO_DATE", line 6
orclz> SELECT my_to_date('02-02-2023') AS result_date FROM dual;
RESULT_DATE
-------------------
0002-02-20:23:00:00
orclz>
|
|
|
|
|
|
| Re: Create a oracle function that parse a string to date time when not null [message #689260 is a reply to message #689253] |
Mon, 30 October 2023 08:09 |
 |
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Your original query takes a string of characters representing a date in a specific format, converts it to a date, then back to a string in a different format. If you want a function that does the same thing, then your function should return VARCHAR2 data type not DATE data type. In the example below, I have modified your function to do that, which also causes it to automatically return null if the input string is null. I have also expanded the exception section to check if the date is already in the desired format and return that, otherwise raise the error. I have then demonstrated each of those things.
-- modified function that you can copy and paste:
CREATE OR REPLACE FUNCTION my_to_date(p_str IN VARCHAR2)
RETURN VARCHAR2
IS
v_date VARCHAR2(10);
BEGIN
v_date := TO_CHAR(TO_DATE(p_str,'mm-dd-yyyy'),
'DD/MM/YYYY');
RETURN v_date;
EXCEPTION
WHEN OTHERS THEN
BEGIN
v_date := TO_CHAR(TO_DATE(p_str,'DD/MM/YYYY'),
'DD/MM/YYYY');
RETURN v_date;
EXCEPTION
WHEN OTHERS THEN
RAISE; -- or however else you want to handle it
END;
END my_to_date;
/
-- demonstration of compilation of function without any errors:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE FUNCTION my_to_date(p_str IN VARCHAR2)
2 RETURN VARCHAR2
3 IS
4 v_date VARCHAR2(10);
5 BEGIN
6 v_date := TO_CHAR(TO_DATE(p_str,'mm-dd-yyyy'),
7 'DD/MM/YYYY');
8 RETURN v_date;
9 EXCEPTION
10 WHEN OTHERS THEN
11 BEGIN
12 v_date := TO_CHAR(TO_DATE(p_str,'DD/MM/YYYY'),
13 'DD/MM/YYYY');
14 RETURN v_date;
15 EXCEPTION
16 WHEN OTHERS THEN
17 RAISE; -- or however else you want to handle it
18 END;
19 END my_to_date;
20 /
Function created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
-- usage demonstrations:
-- convert character string from one date format to another:
SCOTT@orcl_12.1.0.2.0> select my_to_date ('2-23-1999') from dual
2 /
MY_TO_DATE('2-23-1999')
--------------------------------------------------------------------------------
23/02/1999
1 row selected.
-- returns null when character string is null:
SCOTT@orcl_12.1.0.2.0> select my_to_date (null) from dual
2 /
MY_TO_DATE(NULL)
--------------------------------------------------------------------------------
1 row selected.
-- returns same string if already in desired format:
SCOTT@orcl_12.1.0.2.0> select my_to_date ('23/2/1999') from dual
2 /
MY_TO_DATE('23/2/1999')
--------------------------------------------------------------------------------
23/02/1999
1 row selected.
-- raises error when format is not recognized:
SCOTT@orcl_12.1.0.2.0> select my_to_date ('1999-2-23') from dual
2 /
select my_to_date ('1999-2-23') from dual
*
ERROR at line 1:
ORA-01861: literal does not match format string
ORA-06512: at "SCOTT.MY_TO_DATE", line 17
ORA-01843: not a valid month
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
