How to build SQL query using strings whose value should be retrieved from TestCase property

[arasi_m]

Suppose i have the following properties defined in the Test Case Properties

FromTime = 2023-11-15 23:00:00

ToTime = 2023-11-16 23:00:00

I want to replace the time given in the below query with the above test case property variables. How to do that in ReadyAPI JDBC datasource ?

SELECT * FROM Table1 where TagId = 4314 and Time > '2023-11-15 23:00:00' and Time < '2023-11-16 23:00:00'

I am able to retrieve integer values like TagId and use it by adding 'Prepared Properties' within the JDBC datasource window. But if i add the string and use it inside the query, data is not fetched as the string is not interpreted properly.

[mrdwprice]

Perhaps you are missing the single quotation marks?

SELECT * FROM Table1 where TagId = 4314 and Time > '${#Scope#Property-Name}' and Time < '${#Scope#Property-Name}'

[TNeuschwanger]

Hello @arasi_m 

Just to add on a little to what mrdwprice stated previously...

SELECT * FROM Table1 where TagId = ${#TestCase#TagId} and Time > '${#TestCase#FromTimeUTC}' and Time < '${#TestCase#ToTimeUTC}'

A nice feature that ReadyAPI provides is to 'right-click' in most any data entry field and choose the "Get Data" menu item.  It allows you to gather the correct parameter replacement value from virtually anywhere in your project.  In this case, doing that and picking the values from your Test Case area will get you the properties you can use.

Regards,

Todd

[arasi_m]

Thank you for the response. But i was facing the below error

Can't get the Connection for specified properties; com.microsoft.sqlserver.jdbc.SQLServerException: An error occurred during the current command (Done status 0). Conversion failed when converting date and/or time from character string.

When i used the query like this
SELECT * FROM Table1 where TagId = 4314 and Time > '${#TestCase#FromTime}'

[mrdwprice]

What data type does Table1.Time have in your database?
is it DATETIME?

[arasi_m]

Hello,

In the database it is defined like this

Time(datetime2(0),not null)

[mrdwprice]

Try using the CAST or CONVERT function

SELECT * FROM Table1 WHERE TagId = 4314 AND Time > CAST('${#TestCase#FromTime}' AS datetime2)

[arasi_m]

Thank you, my issue got resolved.