+1 vote
1 view
Hi,

At the dataset creation level I would like to add a date type parameter but I find only 4 types: String, Raw, Number, Generic.

Help pleaaaaase
Environment 7.1.3 CE
asked Apr 21, 2020 in Data Set by RahmaTeffahi (870 points) | 1 view
Hi,

at dataset level you have 4 types only to choose and for date type you should choose type "String".

In the future we will improve this handling but at the moment this is the suggested way to manage dates object.

Kind regards

Matteo
Thank you for your response, but it generates an oracle error:

ORA-01861: literal does not match format string

And in the same time i can't convert the field from date to string because i would do a comparison for two dates which is different for two string
You should use string. Make sure that you cast your date field to string with the appropriate format you are using. Date format should be the same with your field and parameter

This is in MySQL but the same concept should apply in Oracle. You need to format your date into string. In my case, I use the format "%Y-%m-%d"  or "YYYY-mm-dd". You should input your parameter in the same format.

If you need to compare 2 dates, you should prepare your statement first. You can use Common Table Expressions or subquery or temporary table in your dataset. I also sometimes use stored procedures in my dataset if more complicated preparation of data is needed. But most of the time, a simple common table expression or subquery will do the trick you needed. YMMV

HI Dawinpasco, 

I need 2 conditions on the dates one an equality so it works if I convert the date to string but the other is less than or equal so I need to convert the parameter again to date. The problem here is that the query is correct but when I click on save it displays an error message "ORA-01861: literal does not match format string" 

The trick is to do a preview before saving and it works like that

Please log in or register to answer this question.

1,553 questions
1,017 answers
2,037 comments
2,567 users