0

I need to convert a WHERE-statement with a TO_DATE-function from Oracle to Informix 10. Unfortunately I have no direct access to the Informix instance and can execute SQL only over a Job Management GUI, which makes testing by try-and-error quite inconvenient.

Anyway, in Oracle it looks like this (for compatibility reasons no NVL2-function):

select * from mytable
where date_column >= (CASE WHEN date_column is not null THEN to_date('13.02.2014 23:00:00', 'dd.mm.yyyy hh24:mi:ss') ELSE to_date('01.04.2010', 'dd.mm.yyyy') END);

I looked up the date notation in Informix here: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.gls.doc/glsug62.htm

and came up with this:

select * from mytable
where date_column >= (CASE WHEN date_column is not null THEN to_date('13.02.2014 23:00:00', '%d.%m.%Y %R:%S') ELSE to_date('01.04.2010', '%d.%m.%Y') END);

For me this looks correct, but the Informix server has another opinion about that.

Could anyone give me a hint?

Ispirer SQLWays Migrations
  • 1,028
  • 9
  • 16
royskatt
  • 1,190
  • 2
  • 15
  • 35

1 Answers1

0

Damn, I forgot a closing apostroph in the Informix-Statement, corrected it above - now it works (self-facepalm).

royskatt
  • 1,190
  • 2
  • 15
  • 35