There is a beauty of an FND API that resides in the apps schema to convert a string into DATE. This API can take a string value in various formats and convert them into Oracle Date. Many times we have to parse a piece of plain text and convert that to date. This is very true when it comes to converting date values from flat file into a proper date column. In this article we will convert a date value from various possible string formats into a "Date Value".
Why use the FND API ?
For example, if you run these SQLs, you can get the following error if you do not explicitly specify the format of the date in to_date
SQL> select to_date( '21-12-2009') as date_value from dual ;
ORA-01843: not a valid month
For example, if you run this SQL, you can get the following error
SQL> select to_date( '21-12-09') as date_value from dual ;
ORA-01843: not a valid month
SQL> select to_date( '2009/12/21') as date_value from dual ;
ORA-01861: literal does not match format string
Now lets try these with fnd_conc_date.string_to_date
To avoid these errors, please find the usage of this API in different flavours. We are converting 23-Dec-2009 as below
+++++++++++++With Hyphens and Mon++++++++++++++++
SQL> select fnd_conc_date.string_to_date( '21-Dec-2009') as date_value from dual ;
DATE_VALUE
-----------------------
21/12/2009
+++++++++++++With hyphens, MM and YYYY++++++++++++++++
SQL> select fnd_conc_date.string_to_date( '21-12-2009') as date_value from dual ;
DATE_VALUE
-----------
21/12/2009
+++++++++++++With hyphens MM and YY++++++++++++++++
SQL> select fnd_conc_date.string_to_date( '21-12-09') as date_value from dual ;
DATE_VALUE
-----------
21/12/2009
++++++++++++No spaces or slashes+++++++++++++++++
SQL> select fnd_conc_date.string_to_date( '21Dec2009') as date_value from dual ;
DATE_VALUE
-----------
21/12/2009
+++++++++++++With forward slashes++++++++++++++++
SQL> select fnd_conc_date.string_to_date( '21/Dec/2009 23:22') as date_value from dual ;
DATE_VALUE
-----------
21/12/2009
+++++++++++++++++++++++++++++
SQL> select fnd_conc_date.string_to_date( '21 Dec 2009 23:22') as date_value from dual ;
DATE_VALUE
-----------
21/12/2009
++++++++++++With YYYY/DD/MM+++++++++++++++++
SQL> select fnd_conc_date.string_to_date( '2009/12/21') as date_value from dual ;
DATE_VALUE
-----------
21/12/2009
Subscribe to this comment's feedwritten by Priya Pusp , November 24, 2009
Regard's
PP
written by Anil Passi- , November 24, 2009
Santhu- The time component is retained by this API
See this
SQL> select to_char(fnd_conc_date.string_to_date( '21/Dec/2009 23:22'),'DD-MON-YYYY HH24:MI:SS') as date_value from dual ;
DATE_VALUE
--------------------------
21-DEC-2009 23:22:00
Thanks,
Anil Passi
written by santhu , November 24, 2009
Need your valuable advice.........
Anil, i am about to start a career in Oracle Financials(modules i am going to work are AP,AR,GL) starting from Dec 1st. I got trained in Oracle Apps Technical and have got some basic idea about some functional modules.
Most of them say that We need Core financial basics to work in O-Financials....but i am from purely technical background.
Request you to let me know how to start and ur valuable suggestions to succeed in this path and how can i go further from here......
Thanks a ton in advance ,
Santhosh.S
written by Vijayakumar , November 25, 2009
Excellent writeup,Thanks for dedicating this article for the requirement mentioned. Thanks in anticipation.
Thanks,
Kasi, Vijayakumar
written by Suresh A , November 25, 2009
Very good article. I have a doubt.
If try below query
SQL> SELECT FND_CONC_DATE.STRING_TO_DATE('06-13-2009') AS DATE_VALUE FROM DUAL;
returns null.
why MM-DD-YYYY (or MMDDYY) formats are not considered in the API.
written by Cheenu , November 25, 2009
Check your output date format for other inputs (DD-MON-YY etc)
If the output date format is MM-DD-YYYY you can't use this api to convert into the same format.
And also go through the API for supported date formates (YYYY/MM/DD, DD-MM-RR, DD-MON-RR, NLS_DATE_FORMAT )
Anil please confirm
written by Anil Passi- , November 26, 2009
Examining their source code is good idea, and you can create a wrapper on top of that API to support additional formats
Cheers
Anil
written by Stalin , November 27, 2009
written by Amr Sabry , December 07, 2009
i've another concern regarding dates ... i've found this API , FND_DATE.DATE_TO_CANONICAL and CANONICAL_TO_DATE .... can you please give me a slight explaination of it..
Many Thanx
Amr Sabry
written by vishal , January 07, 2010
What is the difference between direct & indirect responsibility. in security user form.
please provide information.
Thanks to You
written by abdulrahman , January 10, 2010
written by Sudhamsu Josyam , January 26, 2010
I find this to be most helpful when used with Attribute columns that have date in them
Thanks Anil.
Sudhamsu Josyam
written by Sergey Porokh , February 17, 2010
I think we should add that using of FND_CONC_DATE.STRING_TO_DATE is far slower in DML commands than normal TO_DATE(:p_date_str, :p_date_mask) so long as TO_DATE is a built-in function it is deterministic.
For instance, if we run
SELECT FND_CONC_DATE.STRING_TO_DATE('06-13-2009') AS DATE_VALUE FROM DUAL CONNECT BY LEVEL < 101
this function will be run 100 times. (try to embedd debug messages to FND_CONC_DATE.STRING_TO_DATE).
Anyway thanks for update. All other cases can leverage it.
| < Prev | Next > |
|---|
I have a small doubt ... are there any options in the above said API to display the timestamp also along with date.
Thanks,
Santhosh.S