Skip to Main Content

Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Date in presentation variable to TIMESTAMP conversion

cardelJan 7 2010 — edited Jan 8 2010
Hello,
I have one date column id database table, one date prompt with calendar and I need to show number of days between the DATE_COLUMN and PROMPT_VALUE.
I am storing selected prompt value into presentation variable PROMPT_DATE.
I think, I need to use TIMESTAMPDIFF function, but I have this problem:

TIMESTAMPDIFF(SQL_TSI_DAY,DATE_COLUMN,ANOTHER_DATE_COLUMN) is OK

but

TIMESTAMPDIFF(SQL_TSI_DAY,DATE_COLUMN,CAST ('@{PROMPT_DATE}' as DATE))

I got error:

[nQSError: 10058] A general error has occurred. [nQSError: 46046] Datetime value 4/1/2000 does not match the specified format. (HY000) (...... TIMESTAMPDIFF(SQL_TSI_DAY,Periods."Month", cast('4/1/2000' as date)) ....)

I don´t know how to convert date in presentation variable to TIMESTAMP or another format, which I can use in TIMESTAMPDIFF function.

I have some idea, but I would like to find another solution. One solution might be to parse presentation variable as string, substring 3 parts with day, mont, year and concat this parts to one string, that can be used in TIMESTAMP call
But I will have problem with date format and locales. For example in english I will have mm/dd/yyyy mask, in another locale I will have dd.mm.yyyy mask

Can you help me?
This post has been answered by 269975 on Jan 7 2010
Jump to Answer

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.