Problems with Date Format on Param

I have a query for a data set which works fine, with hard coded values...

SELECT TRUNC(((TO_DATE('25.10.2010 10:00:00', 'DD.MM.YYYY HH24:MI:SS') -
TO_DATE('01.01.1970 00:00:00', 'DD.MM.YYYY HH24:MI:SS')) * 24 * 60 * 60 * 1000 +
((300000) / 2)) / (300000))
FROM dual

I simply want to replace the first hard coded date ('25.10.2010 10:00:00') within this query with a report parameter, but have not been successful. I have tried NUMEROUS different formats.

To me, the most logical format to specify and use for a Date/Time parameter value being substituted is the following...

SELECT TRUNC(((TO_DATE(?, 'yyyy-MM-dd HH:mm:ss.SSS') -
TO_DATE('01.01.1970 00:00:00', 'DD.MM.YYYY HH24:MI:SS')) * 24 * 60 * 60 * 1000 +
((300000) / 2)) / (300000))
FROM dual

Since, this is the default Date/Time format noted for a parameter.
I have also explicitly specified the format of the parameter as Custom with the same format (yyyy-MM-dd HH:mm:ss.SSS) which displays the sample date as expected within the "Preview with Format" for the report parameter.

If I create a separate DataSet with just a straight select for the configured report paramter...

SELECT ? FROM dual

I get the following as a "Preview Results" for the Test DataSet.

2010-10-05 12:30:30.000000000

Having 9 digits display for the SSS format is a bit of a surprise, but at least I get back a value.

The typical error I get, when even simplified and dumbed down to the following for a dataset is "ORA-01810 Format code appears twice"...

SELECT TO_DATE(?,'yyyy-MM-dd HH:mm:ss.SSS') FROM dual

Okay... so the ORA error seems a fairly common issue that the month (MM) and minute (mm) are using the same code and that the minute code should be specified as mi, which would be the following...

SELECT TO_DATE(?,'yyyy-MM-dd HH:mi:ss.SSS') FROM dual

BUT, I get the same error, even when I change the Date/Time Report parameter to being the same Custom format (yyyy-MM-dd HH:mi:ss.SSS).

Note that the "Preview with Format" for the Date/Time Report parameter does not display a sample valid value when the mi format version is specified (yyyy-MM-dd HH:mi:ss.SSS)

This problem should be easy to follow and duplicate, since there is no dependency on any particular sql database, table or column needing to exist.

Thanks in advance for your help.

Find more posts tagged with

Comments

mwilliams

Hi stinky,

So, your issue is trying to format your parameter value in the correct way to pass to your database? If so, you could format the date within script in BIRT and insert it into your query in the beforeOpen method of your dataSet. If not, let me know.

stinky

No, For this DataSet I am not even going to the database, nor even going out to an output widget.

My issue is that the DataSet fails to provide a result, when I attempt to substitute a parameter.
I am not able to provide a proper date format string for the T0_DATE function.

One can quickly and easily copy/paste my SQL I provided into a test DataSet and reproduce the problem/issue.

Here is the one that I believe should work, with subsituting in a standard default Date/Time Report Parameter...

SELECT TRUNC(((TO_DATE(?, 'yyyy-MM-dd HH:mm:ss.SSS') -
TO_DATE('01.01.1970 00:00:00', 'DD.MM.YYYY HH24:MI:SS')) * 24 * 60 * 60 * 1000 +
((300000) / 2)) / (300000))
FROM dual

or, with the ORA error returned, perhaps (which also fails)...

SELECT TRUNC(((TO_DATE(?, 'yyyy-MM-dd HH:mi:ss.SSS') -
TO_DATE('01.01.1970 00:00:00', 'DD.MM.YYYY HH24:MI:SS')) * 24 * 60 * 60 * 1000 +
((300000) / 2)) / (300000))
FROM dual

Stinky

mwilliams

stinky,

I cannot just copy your query and use it as I don't have an ORACLE database to connect to to use the TO_DATE function, so I just get an error. If you have access to string dates, '25.10.2010 10:00:00', you can simple parse them into actual dates in script with something like the following:

df = new Packages.java.text.SimpleDateFormat("dd.MM.yyyy HH:mm:ss");
myDate = df.parse(yourStringDateField);

You can then use "myDate" to build your query in the beforeOpen of your dataSet rather than having to use the TO_DATE function that you're not having success with.

If you have a date item that you want to format into another date format, you can do:

df = new Packages.java.text.SimpleDateFormat("dd.MM.yyyy HH:mm:ss");
myDate = df.parse(yourDateField);

Maybe I'm not understanding what issue you're having, but if it's simply formatting a string date into a date so that you can use in your TRUNC() function in your query, the above should work for you.