# Using time/date tags in SQL WHERE caluse

**URL:** <https://forum.inductiveautomation.com/t/using-time-date-tags-in-sql-where-caluse/11574>\
**Category:** Ignition\
**Created:** [April 25, 2016, 2:55pm UTC](https://forum.inductiveautomation.com/t/using-time-date-tags-in-sql-where-caluse/11574 "2016-04-25T14:55:03Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Mr.K001](https://avatars.discourse-cdn.com/v4/letter/m/ea5d25/32.png) [@Mr.K001](https://forum.inductiveautomation.com/u/Mr.K001)\
**Post date:** [April 25, 2016, 2:55pm UTC](https://forum.inductiveautomation.com/t/using-time-date-tags-in-sql-where-caluse/11574/1 "2016-04-25T14:55:03Z")

</div>

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:

```auto
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 :

```auto
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!

---

<div class="post-metadata">

**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\
**Post date:** [April 25, 2016, 3:14pm UTC](https://forum.inductiveautomation.com/t/using-time-date-tags-in-sql-where-caluse/11574/2 "2016-04-25T15:14:22Z")

</div>

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

```auto
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)
```

---

<div class="post-metadata">

**Author:** ![Mr.K001](https://avatars.discourse-cdn.com/v4/letter/m/ea5d25/32.png) [@Mr.K001](https://forum.inductiveautomation.com/u/Mr.K001)\
**Post date:** [April 25, 2016, 3:16pm UTC](https://forum.inductiveautomation.com/t/using-time-date-tags-in-sql-where-caluse/11574/3 "2016-04-25T15:16:33Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Mr.K001](https://avatars.discourse-cdn.com/v4/letter/m/ea5d25/32.png) [@Mr.K001](https://forum.inductiveautomation.com/u/Mr.K001)\
**Post date:** [April 25, 2016, 3:20pm UTC](https://forum.inductiveautomation.com/t/using-time-date-tags-in-sql-where-caluse/11574/4 "2016-04-25T15:20:27Z")

</div>

[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.