to_date with AS400

Question

I am writing to you because I can't use the operator to_date on an AS400 database.

With Oracle database, I use:

datefield >= to_date('01/01/2014','DD/MM/YYYY')

But with AS400, I get an error:

Incompatible operator

Is there another function I may use to replace to_date?

Answer 1

assuming datefield is a actual date data type

Then all you need to do is use an ISO formatted date string

datefield >= '2014-01-01'

DB2 for IBM i will always recognize '2014-01-01' as a date.

But if you really want to explicitly convert it yourself, then there's two functions

DATE('2014-01-01')
CAST('2014-01-01' as DATE)

CAST is preferred for portability.

I recommend sticking with ISO format, though the system will recognize USA 'mm/dd/yyyy' and EUR 'dd.mm.yyyy'.

Reference here:

http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_71/db2/rbafzdtstrng.htm