SQL UPDATE using datetime problem

Ignition
1

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:

2

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() function.