2

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?

AngocA
  • 7,655
  • 6
  • 39
  • 55
Olivierm67
  • 81
  • 1
  • 1
  • 8
  • 1
    possible duplicate of [Converting a string to a date in DB2](http://stackoverflow.com/questions/4852139/converting-a-string-to-a-date-in-db2) – Adriano Repetti Jul 16 '14 at 09:48
  • I want to specify that I want to put it in a where condition.. – Olivierm67 Jul 16 '14 at 09:51
  • In your WHERE condition, are you trying to convert a database value stored as dd/mm/yyyy into a date for the comparison, or do you plan to compare against a specific date such as dates less than '06/30/2014' – WarrenT Jul 16 '14 at 16:49

2 Answers2

5

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

Charles
  • 21,637
  • 1
  • 20
  • 44
  • Hello, thanks for your response. Can you tell me how to use CAST ? Because I have not been able to use it. I put it in the WHERE condition, like "CAST('2014-01-01' to DATE)" or "CAST(datefield to DATE)" but it doesn't work. Thanks lot ! – Olivierm67 Jul 17 '14 at 07:13
  • @Olivierm67, are you sure that the date field you're comparing is truly a date field? Old-school AS/400 programmers liked to store dates as numeric fields. – Tracy Probst Jul 17 '14 at 12:21
  • @Olivierm67 It would help if you (1) gave us the database definition of `datefield`, (2) told us precisely what you want to accomplish and (3) included any error message IDs and/or SQLSTATE/SQLCODE if some error was returned. Otherwise we're mostly in the dark. – user2338816 Jul 19 '14 at 03:28
  • Of course the OP should have given DDL and been clear about what failure, however: Neither of "`CAST('2014-01-01' to DATE)` or `CAST(datefield to DATE)`" would function because the syntax offered is incorrect; i.e. the "but it doesn't work" would almost surely be described by the msg SQL0199 "Keyword TO not expected. Valid tokens: AS."; thus @Charles, the part of the answer offering CAST should be modified to `CAST('2014-01-01' AS DATE)` p.s. sorry I did not notice that when I added my first necro-post to the topic, but this comment can stand irrespective any update being made. – CRPence Aug 14 '16 at 22:30
0

I realize this topic is old, but the current answer seemed mostly to ignore the original issue with TO_DATE, and instead offer a circumvention; of course the circumvention is IMO, a better approach. By addition of the message identifier and further explanation of the original issue and possible resolutions, hopefully those are beneficial to others in both locating this discussion as a match to their own issue and beneficial for the additional commentary provided.

The issue described in the OP is a reflection of the error condition SQL0401 [sqlcode -401] diagnosing that the data-type of the TO_DATE scalar is a TIMESTAMP whereas the DateField column data-type is a DATE [or so implied, although if the OP had included the DDL for the TABLE, the reviewers could be assured that "datefield" is indeed a column of the DATE data type].

In v5r3 the "Cause" is described by the text "Date, time, and timestamp operands are compatible with character operands or with another operand of the same type."; FWiW the USEnglish [first-level] text likely would have been "Comparison operator >= operands not compatible.", rather than just "Incompatible operator" as was noted in the OP. Even by v7r1, the documentation suggests no change for the SQL0401:
http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/rzala/rzalaml.htm

"...
Date, time, and timestamp operands are compatible with character and graphic operands or with another operand of the same type.
..."

Despite the name of the scalar function, for what might seem the logical effect given that moniker, the scalar result is not a DATE data type; the effect is instead reflective of the scalar function name TIMESTAMP_FORMAT, thus yielding a TIMESTAMP scalar result. The moniker TO_DATE is merely a synonym\syntax-alternative:
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzscatsformat.htm

The originally described scenario datefield >= to_date('01/01/2014','DD/MM/YYYY') for which a non-standard date format is coded, the error could be prevented by explicitly casting the result of that TO_DATE scalar to a DATE type. For example by wrapping the TO_DATE result in another [casting] scalar, such as for example, either of the DATE casting scalar
datefield >= DATE(to_date('01/01/2014','DD/MM/YYYY'))
or the CAST scalar
datefield >= CAST(to_date('01/01/2014','DD/MM/YYYY') as DATE)

Of course the other alternatives of using a character-string formatted as one of the standards date formats [e.g. *ISO as Charles suggested] is probably just as simple; even if that usage is not as explicitly revealing [to a reviewer of the statement] as would be the format-string specified as the second argument on the TO_DATE(). But per the specification originally shown as 'DD/MM/YYYY', the preference may be to use the *EUR standard formatting for which the format is 'DD.MM.YYYY'; i.e. coded as datefield >= '01.01.2014'

Note that in addition to the Date strings documentation reference http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/db2/rbafzdatestrings.htm, there is another alternative not mentioned on that page which is a somewhat redundant form of the *ISO formatted character-string, DATE '2014-01-01' and almost the same [both in specification and redundancy; the alternative merely saves typing the parentheses] as the DATE [casting] scalar specification DATE('2014-01-01') already mentioned elsewhere in this topic. Thus each of datefield >= DATE'2014-01-01' or datefield >= '2014-01-01' or datefield >= DATE('2014-01-01') are all equivalent, and each is depending on *ISO formatting of the character-string as the date representation.

CRPence
  • 1,259
  • 7
  • 12
  • Note that by IBM i 7.3, perhaps earlier, the effect of comparing a TIMESTAMP to a DATE will no longer result in the error "***incompatible operands***" thus rendering moot, the original concern expressed in the OP – CRPence Oct 13 '16 at 22:12