Informix Current Date Value - SQL Update

[OrWolf]

I'm attempting to update a row in an Informix database through a SQL task. I have the following line of code working:

UPDATE SYNC SET LASTSYNCEDATE = '1-3-2007' WHERE TABLENAME = 'X'

My problem is that I can't seem to find something similar to Now() or GetDate() in the Informix world. Any help would be GREATLY appreciated.

 

[OrWolf]

Something else I thought of is that I can also query a secondary table for the maximum of a change date field. Here I would just need to know how to query the Max value.

Thanks again.

 

[mberni]

the informix keyword for "now" is "current"

so you could so:

UPDATE SYNC SET LASTSYNCEDATE = current WHERE TABLENAME = 'X';

but be careful: in a stored procedure environment this value is only updated once per procedurecall, so within a loop it will always have the same value.

 

[mberni]

concerning the "max-question"

i do not really understand what you exactly want to do, but a syntax for your problem would be something like that:

select * from table1
where datefield = (select max(otherdatefield) from table2);

 

[OrWolf]

I had tried to use current but figured it was not valid since it returns the following error:

Error Source: Microsoft OLE DB Provider for ODBC Drivers
Error Description: [Ardent][UVODBC][1400832]Error ID: 29 Severity: ERROR Facility: FPSRVERR - Line 3, column 35 (around "current"): Syntax error.

 

[mberni]

please check your syntax and/or post your statement containing current.

it must look like this:

update table
set field=current ...

and NOT

update table
set field = "current" ...

 

[PHV]

And what about this ?
UPDATE SYNC SET LASTSYNCEDATE = TODAY WHERE TABLENAME = 'X'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

OrWolf, just to know, didn't TODAY (instead of CURRENT) solve your issue ?

 

[OrWolf]

TODAY was also returned as a syntax error. I ended up having to scrap the update and find an alternate work around.

 

[PHV]

Seems more like an ODBC driver issue than an Informix syntax.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886