Selecting Records in SQL database with DATE

Ignition
1

I am getting the following “ERROR: Conversion failed when converting date and/or time from character string.” when I attempt to select records from SQLExpress database based on the date. Specifically, I only want records that were added in the last 24 hours.

image
image925×269 7.45 KB

I know I am missing something basic, but I haven’t determined a solution. My report seems to be displaying the entire database that began in July 2015 which is thousands of records if I am not selecting by date.

StartDate and EndDate are both Date type and .Time_Stamp is also a datetime format in the database.

2

EDIT: Although I should be asking what version of Ignition.

Try putting in question marks and put the parameters below.

image
image1066×895 37.6 KB

1 Like
3

I will try that. I went to an SQL function:

[Interval].Time_Stamp between (select dateadd(hour, -24, getdate())) and (select dateadd(hour, +0, getdate()));

that seems to work though…I’m interested in trying your solution.

4

It does work as well! The ? for the parameters is new to me. I haven’t used the parameters in my report queries before. Thanks!

1 Like