Updating date field via sql

[berryt214]

Hi,

I'm trying to update a date field in my progress database but I seem to be having trouble finding the correct syntax. On other forums, I found just using the single quotes around the date was sufficient enough but I'm still getting an error. Here's the query and error that I'm getting:

UPDATE PUB.orders SET PUB.orders.orderdate = '01/02/2012', PUB.orders.accountid = 999991, PUB.accounts.Salerepid = 47, PUB.orders.pages = 8, PUB.orders.unit = '', PUB.orders.keyphrase = 'Master - Town & Country', PUB.orders.memo = '', PUB.orders.qtyrequired = 56665, PUB.orders.qtybilled = 0 WHERE PUB.orders.accountid = PUB.accounts.accountid AND PUB.orders.orderid = 91

ERROR [HY000] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about ".orders.orderdate = '01/02/2012', PUB.or" (10713)


also, under the data dictionary, it states the orderdate field is data type date and the format is "99/99/9999".


EDIT: This code is written as a string in vb.net. I've seen alternative ways of updating dates using ABL programming language but unfortunately, i need it to be in a SQL statement.

Thanks,
berryt214

 

[SergioC]

Hi, try it:

[COLOR=#333333]SET PUB.orders.orderdate = [B]DATE('01/02/2012')[/B][/COLOR]

Regards.

 

[berryt214]

sorry. It didn't work. Still getting the same error.

 

[Stefan]

See this thread for how dates can be used in SQL92.