# Select DATEADD()

**URL:** <https://forum.inductiveautomation.com/t/select-dateadd/44458>\
**Category:** Ignition\
**Tags:** database\
**Created:** [March 15, 2021, 7:24am UTC](https://forum.inductiveautomation.com/t/select-dateadd/44458 "2021-03-15T07:24:27Z")\
**Posts on this page:** 18\
**Page:** 1

<div class="post-metadata">

**Author:** ![OscarLucas](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/oscarlucas/32/6417_2.png) [@OscarLucas](https://forum.inductiveautomation.com/u/OscarLucas)\
**Post date:** [March 15, 2021, 7:24am UTC](https://forum.inductiveautomation.com/t/select-dateadd/44458/1 "2021-03-15T07:24:27Z")

</div>

Hello,  
I want to get the initial time + the time inverted in the task as a column.  
I am using dateadd. I don’t know if is the way, and i don’t understand where can be the mistake. I am following the example.  
[https://docs.inductiveautomation.com/display/DOC79/Data+Partitioning+and+Pruning](https://docs.inductiveautomation.com/display/DOC79/Data+Partitioning+and+Pruning)

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/3/c372e873e7576f4139f1698093497cbdba0a8b46.png)  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/0/2053add719ae0247a29d8144e56bc02d4c73665c.png)

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [March 15, 2021, 7:53am UTC](https://forum.inductiveautomation.com/t/select-dateadd/44458/2 "2021-03-15T07:53:41Z")

</div>

What is the datatype of _tiempo\_invertido_? It needs to be numeric and not a TIME variable.

I like to use [https://www.w3schools.com/sql/func\_sqlserver\_dateadd.asp](https://www.w3schools.com/sql/func_sqlserver_dateadd.asp) for testing SQL scripts.

---

<div class="post-metadata">

**Author:** ![peter](https://avatars.discourse-cdn.com/v4/letter/p/278dde/32.png) [@peter](https://forum.inductiveautomation.com/u/peter)\
**Post date:** [March 15, 2021, 8:46am UTC](https://forum.inductiveautomation.com/t/select-dateadd/44458/3 "2021-03-15T08:46:50Z")

</div>

What database are you using? The error messages says:

> caused by SQLSyntaxErrorException: (conn=91837) FUNCTION tallerelectrico.dateadd does not exist

The DATEADD function is available in MSSQL (as mentioned in the link you provided), but it does not exist in MySQL or Oracle databases.

* * *

**Oracle**

I'm not really up on Oracle, but [this link](https://stackoverflow.com/q/26510033/31326) shows various methods you can use to add 1 hour to column. I like [this example](https://stackoverflow.com/a/26510164/31326):

```auto
select some_date_column + interval '1' hour 
from your_table;

```

NOTE that the actual amount of time ('1') is specified as a string

* * *

**MySQL**

For MySQL you would use the [DATE\_ADD()](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-add) function, EG:

`SELECT DATE_ADD("2017-06-15", INTERVAL 1 HOUR);`

---

<div class="post-metadata">

**Author:** ![OscarLucas](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/oscarlucas/32/6417_2.png) [@OscarLucas](https://forum.inductiveautomation.com/u/OscarLucas)\
**Post date:** [March 15, 2021, 9:39am UTC](https://forum.inductiveautomation.com/t/select-dateadd/44458/4 "2021-03-15T09:39:34Z")

</div>

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/b/6b6e6975db796ba0e0238f73c0e6ac967db54645.png)  
work with numbers but do not work with other column. i don’t get any system error.  
tiempo\_invertido = int = hours = 4 for example

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [March 15, 2021, 11:29am UTC](https://forum.inductiveautomation.com/t/select-dateadd/44458/5 "2021-03-15T11:29:34Z")

</div>

You aren’t providing a numeric value to the query but instead the string ‘tiempo\_invertido’, which your DB undoubtedly doesn’t know how to handle.

Try something like this to use a variable value:

```auto
SELECT referencia_instalacion as ItemId,
count(tarea,' ',usuario) as label,
fecha_inicio as startDate,
fecha_inicio + numtodsinterval({Path to property or tag},'hour') as EndDate
FROM tareas

```

More on the [numtodsinterval](https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions103.htm) function.

---

<div class="post-metadata">

**Author:** ![OscarLucas](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/oscarlucas/32/6417_2.png) [@OscarLucas](https://forum.inductiveautomation.com/u/OscarLucas)\
**Post date:** [March 15, 2021, 12:36pm UTC](https://forum.inductiveautomation.com/t/select-dateadd/44458/6 "2021-03-15T12:36:59Z")

</div>

whit the number works well and tags, but i want to select the time that takes the task to be completed. That is on the data base…

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/2/62601d36d87b5d2e22dd809ebc2cba0a18fbd1c3.png)

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [March 15, 2021, 12:52pm UTC](https://forum.inductiveautomation.com/t/select-dateadd/44458/7 "2021-03-15T12:52:59Z")

</div>

Just use the column name in the function

```auto
SELECT referencia_instalacion as ItemId,
count(tarea,' ',usuario) as label,
fecha_inicio as startDate,
fecha_inicio + numtodsinterval(tiempo_invertido,'hour') as EndDate
FROM tareas

```

---

<div class="post-metadata">

**Author:** ![OscarLucas](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/oscarlucas/32/6417_2.png) [@OscarLucas](https://forum.inductiveautomation.com/u/OscarLucas)\
**Post date:** [March 17, 2021, 7:06am UTC](https://forum.inductiveautomation.com/t/select-dateadd/44458/8 "2021-03-17T07:06:12Z")

</div>

Looks like do not recognize the numtodsinterval, the ‘tiempo invertido’ is not a date. is just am integer.  
Thanks for your help.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/1/a12ac88e34dbe2f35d76970c018017dbe038442a.png)

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [March 17, 2021, 12:24pm UTC](https://forum.inductiveautomation.com/t/select-dateadd/44458/9 "2021-03-17T12:24:25Z")

</div>

What flavor of DB are you using?

`numtodsinterval` is an Oracle function.

---

<div class="post-metadata">

**Author:** ![OscarLucas](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/oscarlucas/32/6417_2.png) [@OscarLucas](https://forum.inductiveautomation.com/u/OscarLucas)\
**Post date:** [March 17, 2021, 1:29pm UTC](https://forum.inductiveautomation.com/t/select-dateadd/44458/10 "2021-03-17T13:29:13Z")

</div>

Mysql - I was using interval …

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/9/99e0529be5c9d8264b53e1e96e0fdd97ab0288f8.png)

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [March 17, 2021, 1:42pm UTC](https://forum.inductiveautomation.com/t/select-dateadd/44458/11 "2021-03-17T13:42:16Z")

</div>

Change numtodsinterval to interval in your query.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [March 17, 2021, 2:10pm UTC](https://forum.inductiveautomation.com/t/select-dateadd/44458/12 "2021-03-17T14:10:58Z")

</div>

For MySql the query should look like this:

```auto
SELECT referencia_instalacion as ItemId,
count(tarea,' ',usuario) as label,
fecha_inicio as startDate,
fecha_inicio + INTERVAL tiempo_invertido HOUR as EndDate
FROM tareas

```

Your first attempt at this didn’t work because you used quotes around the column name making it a string literal which INTERVAL in MySQL can’t use.

---

<div class="post-metadata">

**Author:** ![OscarLucas](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/oscarlucas/32/6417_2.png) [@OscarLucas](https://forum.inductiveautomation.com/u/OscarLucas)\
**Post date:** [March 18, 2021, 7:08am UTC](https://forum.inductiveautomation.com/t/select-dateadd/44458/13 "2021-03-18T07:08:56Z")

</div>

Finally I don’t get any failure, but the data type looks that has been changed in an strange format.  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/c/ac004b9919901b1fa89c619bbb9d7cc4d5b02d58.png)

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/e/1ea60d1f1cca08cdb3957392e6534a8d05e88057.png)  
And the time 1h, has been added in the seconds, instead than in the hours.  
🥴  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/5/25bc8f2ffb7fd517b160d43157647a7f9214380a.png)  
It is a good idea?  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/5/d5a02c8129e5f7c0dc7f5f787b15de14c2a1bb06.png)

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [March 18, 2021, 9:29am UTC](https://forum.inductiveautomation.com/t/select-dateadd/44458/14 "2021-03-18T09:29:07Z")

</div>

Try as @lrose suggested:

```sql
fecha_inicio + INTERVAL tiempo_invertido HOUR as EndDate
```

---

<div class="post-metadata">

**Author:** ![OscarLucas](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/oscarlucas/32/6417_2.png) [@OscarLucas](https://forum.inductiveautomation.com/u/OscarLucas)\
**Post date:** [March 18, 2021, 11:02am UTC](https://forum.inductiveautomation.com/t/select-dateadd/44458/15 "2021-03-18T11:02:37Z")

</div>

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/3/c36e1522d30047d7981a7e8a8f3a370f9537fc5c.png)

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [March 18, 2021, 11:30am UTC](https://forum.inductiveautomation.com/t/select-dateadd/44458/16 "2021-03-18T11:30:05Z")

</div>

You left the comma “,” between the column name and HOUR.

---

<div class="post-metadata">

**Author:** ![OscarLucas](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/oscarlucas/32/6417_2.png) [@OscarLucas](https://forum.inductiveautomation.com/u/OscarLucas)\
**Post date:** [March 18, 2021, 11:40am UTC](https://forum.inductiveautomation.com/t/select-dateadd/44458/17 "2021-03-18T11:40:18Z")

</div>

something very small makes the difference. now is working 😋

---

<div class="post-metadata">

**Author:** ![peter](https://avatars.discourse-cdn.com/v4/letter/p/278dde/32.png) [@peter](https://forum.inductiveautomation.com/u/peter)\
**Post date:** [March 18, 2021, 1:44pm UTC](https://forum.inductiveautomation.com/t/select-dateadd/44458/18 "2021-03-18T13:44:44Z")

</div>

I am very aware that in a program that is 10’s or 100’s of Megabytes in size, your day can be ruined by a single BIT being the wrong way around 😉