Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2012
    Posts
    2

    Unanswered: Problem with Date query under DB2/400

    Hi all, I have a numeric field wich is supposed to represent a date. Somehow a developer thought that a mixed form of 'ddmmyyyy' and 'dmmyyyy' was ok to represent a date.

    So to compare dates I want to create a query using DATE, SUBSTR, LENGTH and LTRIM functions to create a date-like string but the query gives an error

    Code:
    SELECT *
    FROM QS36F/MATECPRA
    WHERE DATE(
    SUBSTR(fechoc, LENGTH(LTRIM(fechoc))-3, 4) || '-' ||
    SUBSTR(fechoc, LENGTH(LTRIM(fechoc))-5, 2) || '-' ||
    SUBSTR(fechoc, 1, LENGTH(LTRIM(fechoc))-6)
    ) >= '2011-01-01'
    If I make this query it works ok

    Code:
    SELECT DATE(
    SUBSTR(fechoc, LENGTH(LTRIM(fechoc))-3, 4) || '-' || 
    SUBSTR(fechoc, LENGTH(LTRIM(fechoc))-5, 2) || '-' || 
    SUBSTR(fechoc, 1, LENGTH(LTRIM(fechoc))-6)
    )
    FROM QS36F/MATECPRA
    Both scrips give error running in a PHP scrip against an iSeries server or being executed in the SQL prompt using the iSeries Telnet terminal (STRSQL)

    The PHP error is

    Warning: odbc_exec(): SQL error: [unixODBC], SQL state S1000 in SQLExecDirect in /srv/www/htdocs/intranet/qry.php on line 39 Error en odbc_exec [unixODBC]
    The error shown by the SQL prompt is

    Error en la selección que tiene relación con el campo FECHOC.
    Any help will be much appreciated,

    Francisco

  2. #2
    Join Date
    Mar 2012
    Posts
    2
    I've already solved it.

    Thanks,

    Francisco

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •