Please Change the syntax of sql query that will be supported by jaspersoft server

[girish.gandhi]

Hi Team ,

I want to publish a report using jaspersoft studio . As i am new to jaspersoft , I dont have much knowledge about its syntax for writing the sql query that will contain the parameters. I have the following query written and is currently working on sql management studio . The Query filters the records between given specified date and time range based on the condtions. I want the below mentioned query to get its syntax changed that will work sucessfully and will be supported in jaspersoft studio to publish report.

................................................................................................................................................................................................................................................................................................................................................................................

DECLARE @Start_Date DATE='2018-08-14'
DECLARE @S_TIME TIME='09:00:0.000'
DECLARE @E_TIME TIME='17:00:0.000'
DECLARE @E_DATE DATE='2018-10-24'
DECLARE @DateRange Int=1
DECLARE @Business_Hours INT=1
if(@DateRange=1 and @Business_Hours=1)
select *from sampletable where cast(sampletime as date) between @Start_Date and @E_Date and
cast(sampletime as time) between @S_TIME and @E_TIME
else if(@DateRange=0 and @Business_Hours=1)
select *from sampletable where cast(sampletime as time) between @S_TIME and @E_TIME
else if(@DateRange=1 and @Business_Hours=0)
select *from sampletable where cast(sampletime as date) between @Start_Date and @E_Date
else if(@DateRange=0 and @Business_Hours=0)
select *from sampletable
...........................................................................................................................................................................................................................................
Explanation -

Query prints records from the table using if else condition . Whatever condition evaluates true query will filter records between give date period and given time period.
 Query uses @DateRange Int=1 , @Business_Hours INT=1 as a flag variable .

sampletime is the DATETIME type field in my table sampletable

DECLARE @Start_Date DATE='2018-08-14'
DECLARE @S_TIME TIME='09:00:0.000'
DECLARE @E_TIME TIME='17:00:0.000'
DECLARE @E_DATE DATE='2018-10-24'
DECLARE @DateRange Int=1
DECLARE @Business_Hours INT=1

These Variables values, I need to take from parameters and using input controls created in the report. If we change the values of @DateRange , @Business_Hours variables query result will get changed.

Please help .

Thanks in Advance

[dszeto]

If you are using the same JDBC driver in JasperStudio as in your SQL Management Studio, does the query still fail?

[girish.gandhi]

@dszeto yes dear , query still fails . I am using mssql-jdbc-7.0.0.jre8 driver .