Date in presentation variable to TIMESTAMP conversion
cardelJan 7 2010 — edited Jan 8 2010Hello,
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?