SQL Datetime Format Problem

skosmas · 2017-03-15 08:42:53

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)

eduardo · 2017-03-15 11:58:15

Hi skosmas,

could you please post the query you are using and the parameter configuration ?

Cheers,
Eduardo

skosmas · 2017-03-15 13:07:01

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)

eduardo · 2017-03-15 13:11:57

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

skosmas · 2017-03-15 13:44:02

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)

skosmas · 2017-03-17 11:27:52

Any news on that?

eduardo · 2017-03-17 11:30:52

Hi skosmas,

not yet, we will update the forum.

Cheers,
Eduardo

eduardo · 2017-03-23 10:08:38

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

skosmas · 2017-03-27 08:12:43

By default unforunately I don't have this format.

Could it be a matter of the version of BIRT?

Which version are you using?

eduardo · 2017-03-27 10:00:59

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

skosmas · 2017-03-27 13:51:39

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.

eduardo · 2017-03-29 07:45:24

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

skosmas · 2017-03-29 09:14:57

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.

eduardo · 2017-03-29 11:28:39

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

skosmas · 2017-03-29 11:43:55

Yes, that works too. Thank you.

ReportServer Forum

#1 2017-03-15 08:42:53

SQL Datetime Format Problem

#2 2017-03-15 11:58:15

Re: SQL Datetime Format Problem

#3 2017-03-15 13:07:01

Re: SQL Datetime Format Problem

#4 2017-03-15 13:11:57

Re: SQL Datetime Format Problem

#5 2017-03-15 13:44:02

Re: SQL Datetime Format Problem

#6 2017-03-17 11:27:52

Re: SQL Datetime Format Problem

#7 2017-03-17 11:30:52

Re: SQL Datetime Format Problem

#8 2017-03-23 10:08:38

Re: SQL Datetime Format Problem

#9 2017-03-27 08:12:43

Re: SQL Datetime Format Problem

#10 2017-03-27 10:00:59

Re: SQL Datetime Format Problem

#11 2017-03-27 13:51:39

Re: SQL Datetime Format Problem

#12 2017-03-29 07:45:24

Re: SQL Datetime Format Problem

#13 2017-03-29 09:14:57

Re: SQL Datetime Format Problem

#14 2017-03-29 11:28:39

Re: SQL Datetime Format Problem

#15 2017-03-29 11:43:55

Re: SQL Datetime Format Problem

Board footer