Using time/date tags in SQL WHERE caluse

Ignition
1

Hey guys, I use different formats of expression tags(time intervals) in my SQL WHERE clause. I get this error when I open a window that uses a query:

SELECT 
	t_stamp,
	column1 AS 'part1',
	column2 AS 'part2'
FROM
	table
WHERE
        t_stamp BETWEEN 'date1' AND 'date2';

When I open a window that has this query in the background I get an error :

Exception: Error running query:
SQLQuery(query=SELECT 
	t_stamp,
	column1 AS 'part1',
	column2 AS 'part2
FROM
        table
WHERE
	t_stamp BETWEEN '-1' AND '-1';
, database=Database)@3600000ms
On: Main Menu.Root Container.Chart.Data
	caused by GatewayException: Conversion failed when converting date and/or time from character string.
	caused by SQLServerException: Conversion failed when converting date and/or time from character string.

Ignition v7.8.2 (b2016030813)
Java: Oracle Corporation 1.8.0_91

Now I did some research into this and few places suggested it might have something to do with the format of my time stamp. right now I use the format yyyy-MM-dd hh:mm:ss. I’ve tried both string type and date time type but my issue is I can’t use string for both tags because in one do some date arithmetic and it wont let you do that from a string data type tag and have to use a date time format.
I guess what I’m trying to get to is if you guys use tags for your WHERE clause, what kind of format do you use? Thanks for the help in advance!

2

in scripting I use the following to set date strings to valid date formats

from java.text import SimpleDateFormat

inputFormat=SimpleDateFormat("yyyy-MM-dd HH:mm:ss")  # Must match the format of the input string
outputFormat=SimpleDateFormat("yyyy-MM-dd HH:mm:ss")        # The format you want to convert to

dateIn=event.source.selectedStringValue

processedDateIn=SimpleDateFormat.parse(inputFormat,dateIn)  # Convert Input String to Java Date

event.source.parent.getComponent('Power Table').SDate=processedDateIn
event.source.parent.getComponent('Power Table').EDate=system.date.addMinutes(processedDateIn,7)
3

Quick Update:
I tried messing with it and instead of BETWEEN I used t_stamo > and t_stamp < and didn’t get the error, oh and also changed my format to yyyy-MM-dd HH:mm:ss from yyyy-MM-dd hh-mm-ss aa
if anyone has a more efficient suggestion I’m all ears, if not, I’ll use the new setup that seems to be working.

1 Like
4

[quote=“MMaynardUSG”]in scripting I use the following to set date strings to valid date formats

[code]
from java.text import SimpleDateFormat

inputFormat=SimpleDateFormat(“yyyy-MM-dd HH:mm:ss”) # Must match the format of the input string
outputFormat=SimpleDateFormat(“yyyy-MM-dd HH:mm:ss”) # The format you want to convert to

dateIn=event.source.selectedStringValue

processedDateIn=SimpleDateFormat.parse(inputFormat,dateIn) # Convert Input String to Java Date

event.source.parent.getComponent(‘Power Table’).SDate=processedDateIn
event.source.parent.getComponent(‘Power Table’).EDate=system.date.addMinutes(processedDateIn,7)
[/code][/quote]

Thanks for the quick response. In this case I don’t use any scripting. just use my expression tag refrences in my SQL query. as I mentioned in my latest reply the problem seems to have been fixed with the couple of tweaks I made so we’ll see how that goes.