# SQL UPDATE using datetime problem

**URL:** <https://forum.inductiveautomation.com/t/sql-update-using-datetime-problem/2978>\
**Category:** Ignition\
**Created:** [May 29, 2011, 2:49pm UTC](https://forum.inductiveautomation.com/t/sql-update-using-datetime-problem/2978 "2011-05-29T14:49:04Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Brett\_Yeomans](https://avatars.discourse-cdn.com/v4/letter/b/35a633/32.png) [@Brett\_Yeomans](https://forum.inductiveautomation.com/u/Brett_Yeomans)\
**Post date:** [May 29, 2011, 2:49pm UTC](https://forum.inductiveautomation.com/t/sql-update-using-datetime-problem/2978/1 "2011-05-29T14:49:04Z")

</div>

I have a datetime dynamic property that is set by an SQL query (dateadd etc). When I try and use this in a button SQL UPDATE I get the following error “Conversion failed when converting datetime from character string.”.  
This is the code behind the button :

queryValue =event.source.parent.Correct\_start  
#queryValue = event.source.parent.getComponent(‘test’).text  
Row = event.source.parent.Period  
system.db.runUpdateQuery(“UPDATE calendar SET startdate = ‘%s’ where “row number” = %s” % (queryValue, Row), ‘SQLServer’)

If I instead move the # in the above expression to the top line the SQL UPDATE works. The .text it refers to in line 2 gets its date from the exact same SQL query that the dynamic property fails on.  
 ![:scratch:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/76d1620bec252bfac07050c889ff5a91dc8a9c25.gif ":scratch:")

---

<div class="post-metadata">

**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\
**Post date:** [May 30, 2011, 5:13am UTC](https://forum.inductiveautomation.com/t/sql-update-using-datetime-problem/2978/2 "2011-05-30T05:13:08Z")

</div>

The problem is that you’re using Python string conversion to put an actual date time into a string-based SQL query. You can see for yourself why that isn’t going to work if you print out the query itself before you try and execute it. The date in the query will look like this:

[tt]Sun May 29 22:04:18 PDT 2011[/tt]

This string will confuse your database - it can’t understand it as a date.

You can convert the date to a proper string with the [system.db.dateFormat()](http://www.inductiveautomation.com/support/usermanuals/ignition/index.html?system_db_dateformat.htm) function.