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
Apps2Fusion Articles 
Comments
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
R egard's
PP
Santhu- The time component is retained by this API
See this
Quote:
Thanks,
Anil Passi
Nee d your valuable advice.........
Anil, i am about to start a career in Oracle Financials(modu les 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.
Re quest 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......
Tha nks a ton in advance ,
Santhosh.S
Excellen t writeup,Thanks for dedicating this article for the requirement mentioned. Thanks in anticipation.
Thanks,
Kasi, Vijayakumar
Very good article. I have a doubt.
If try below query
SQL> SELECT FND_CONC_DATE.S TRING_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.
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
Examining their source code is good idea, and you can create a wrapper on top of that API to support additional formats
Cheers
Anil
i've another concern regarding dates ... i've found this API , FND_DATE.DATE_T O_CANONICAL and CANONICAL_TO_DA TE .... can you please give me a slight explaination of it..
Many Thanx
Amr Sabry
What is the difference between direct & indirect responsibility. in security user form.
please provide information.
T hanks to You
I find this to be most helpful when used with Attribute columns that have date in them
Thanks Anil.
Sudhamsu Josyam
I think we should add that using of FND_CONC_DATE.S TRING_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.S TRING_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.S TRING_TO_DATE).
Anyway thanks for update. All other cases can leverage it.
I always enjoy reading your articles and is my go to source for information
Thanks,
Somnath.
RSS feed for comments to this post