You are not logged in.
Pages: 1
I have a problem with the format of the datetime parameters.
The parameters are returned in greek characters(I live in Greece) and the query in database doesn't return any rows for that reason.
So the current format given to the query is:
1 Φεβ 2017, 12:00 π.μ.
Φεβ is Feb and π.μ. is AM in greek.
I would like the format to be
2017-02-01 00:00
If that's not possible at least the query returns rows with the following format
1 Feb 2017, 12:00 AM
Is there a configuration file that I can fix that??
EDIT: If the parameter is set as only date then the format used is the one I want, this means 2017-02-01
Last edited by skosmas (2017-03-15 09:07:35)
Offline
Hi skosmas,
could you please post the query you are using and the parameter configuration ?
Cheers,
Eduardo
Offline
This is the query that reportserver polls the db:
SELECT *
FROM
db_table AS QT
WHERE
QT.SCAN_TIMESTAMP>=UNIX_TIMESTAMP(STR_TO_DATE('1 Ιαν 2017, 10:53 π.μ.', '%e %b %Y, %h:%i %p'))
AND QT.SCAN_TIMESTAMP<=UNIX_TIMESTAMP(STR_TO_DATE('15 Μαρ 2017, 10:54 π.μ.', '%e %b %Y, %h:%i %p'))
ORDER BY QT.SCAN_TIMESTAMP
If it would be like the following it would work:
SELECT *
FROM
db_table AS QT
WHERE
QT.SCAN_TIMESTAMP>=UNIX_TIMESTAMP(STR_TO_DATE('1 Jan 2017, 10:53 AM', '%e %b %Y, %h:%i %p'))
AND QT.SCAN_TIMESTAMP<=UNIX_TIMESTAMP(STR_TO_DATE('15 Mar 2017, 10:54 AM', '%e %b %Y, %h:%i %p'))
ORDER BY QT.SCAN_TIMESTAMP
But if possible I would like to change the format of datetime parameter so the query would be
SELECT *
FROM
db_table AS QT
WHERE
QT.SCAN_TIMESTAMP>=UNIX_TIMESTAMP('2017-01-01 10:53')
AND QT.SCAN_TIMESTAMP<=UNIX_TIMESTAMP('2017-03-15 22:54')
ORDER BY QT.SCAN_TIMESTAMP
The parameters are simply 2 datetime parameters.
Last edited by skosmas (2017-03-15 13:07:22)
Offline
Hi skosmas,
I ask because datetime params are always being sent as '2017-03-15' in my testcases: the time is being lost. https://forum.reportserver.net/viewtopic.php?pid=2668 and https://forum.reportserver.net/viewtopic.php?id=631
But in your case the time component is being sent, so I need to know your exact configuration, how you configured your datatime params, the database your are using, etc.
What is the original query? Not the query arriving in the database, but the query you entered in reportserver.
Cheers,
Eduardo
Offline
I use Birt to design my reports. So the query for the dataset in birt designer is the following:
SELECT *
FROM
db_table AS QT
WHERE
QT.SCAN_TIMESTAMP>=UNIX_TIMESTAMP(STR_TO_DATE(?, '%e %b %Y, %h:%i %p'))
AND QT.SCAN_TIMESTAMP<=UNIX_TIMESTAMP(STR_TO_DATE(?, '%e %b %Y, %h:%i %p'))
The database is a mysql database.
The parameters in Birt are set as Date Time type and in reportserver as Date/Time parameters in "Date and Time" mode.
What else do you need me to provide?
EDIT: The STR_TO_DATE(?, '%e %b %Y, %h:%i %p') is a modification I added when I saw the format of the query that was requested in db.
The correct query for my report should be:
SELECT *
FROM
db_table AS QT
WHERE
QT.SCAN_TIMESTAMP>=UNIX_TIMESTAMP(?)
AND QT.SCAN_TIMESTAMP<=UNIX_TIMESTAMP(?)
but that requires a "yyyy-MM-dd HH:mm" format
Last edited by skosmas (2017-03-15 13:55:04)
Offline
Any news on that?
Offline
Hi skosmas,
not yet, we will update the forum.
Cheers,
Eduardo
Offline
Hi skosmas,
I have a simple BIRT report running in a Mysql connection, with parameters as you described. My query is the following:
select mycolumn
from myTable
where myDateColumn < ?
In the mysql logs I see the following query arriving:
select mycolumn
from myTable
where myDateColumn < '2017-03-23 10:59:16'
which is in the correct format.
How are you passing the arguments? Is it a simple BIRT report, and you pass the parameters by the standard way? Or are you passing the parameters by URL or any other way?
Cheers,
Eduardo
Offline
By default unforunately I don't have this format.
Could it be a matter of the version of BIRT?
Which version are you using?
Offline
Hi skosmas,
I don't think the BIRT version would influcence this, but here is my version:
BIRT Framework 4.6.0.v201606072122
In reportserver, I am using the BIRT libraries from 3.0.2.
Could you please try with a simple Jasper report?
Cheers,
Eduardo
Offline
I have the same version in BIRT so I have to check it more.
I created the same report in Jasper and the format is the correct but I have set the parameter as java.sql.Timestamp which ReportServer doesn't recognize.
I have found the same issue here: https://forum.reportserver.net/viewtopic.php?id=631
and I am trying to make to work but no luck so far.
I get the following query in db server:
SELECT QT.SNAPSHOT, QT.SCAN_TIMESTAMP
FROM
qrpartol_transactions AS QT
WHERE
QT.SCAN_TIMESTAMP>=UNIX_TIMESTAMP(null)
This means that the parameter returns null value.
So that's also something that I have to check more.
Offline
Hi skosmas,
In the link you pointed (https://forum.reportserver.net/viewtopic.php?id=631), #2: "java.util.Date should be the correct setting". So java.sql.Timestamp is not correct.
Yet, with java.util.Date, I am seeing the problem with jasper:
if you define this jasper query:
select * from mytable where myfield < $P{param1}
then it becomes:
select * from mytable where myfield < '2017-03-29'
So the time component is being cut of.
If you define this jasper query:
select * from mytable where myfield < $P!{param1}
then it becomes:
select * from mytable where myfield < Wed Mar 29 09:44:07 CEST 2017
So maybe you have a similar setting in BIRT. Could you please take a look?
Cheers,
Eduardo
Offline
I have found a workaround for the BIRT designs (I'll look for a better solution in the future).
I've added an extra parameter that uses the BIRT Formatter.format function to create the correct format.
In the "Default value" of the new parameter I added the following expression: Formatter.format(params["fromDateTime"].value, "yyyy-MM-dd HH:mm")
and use the new parameter in the query to the database.
Offline
Hi skosmas,
would a java.sql.Timestamp work too? It may be better to convert the java.util.Date to a java.sql.Timestamp instead of formatting the value to text.
So your second parameter would be of type=java.sql.Timestamp with a "default value" of new java.sql.Timestamp(params["fromDateTime"].getTime())
Cheers,
Eduardo
Offline
Yes, that works too. Thank you.
Offline
Pages: 1