Get data from past 5 days

[mistektips]

Hi,
I have to get data from past 5 days from AS400 table.
I tried using this query:

SELECT     *
FROM         TESTSQL.C27AUDIT
WHERE     TESTSQL.C27AUDIT.C0027_DATE >= CURRENT (DATE) - 5

But it gives me an error.
Also, I just wanted to clarify that the C0027_DATE field is a numeric field.
Could someone suggest how should I accomplish this task?
Any help will be appreciated.
Thanks in advance.

 

[flapeyre]

You can't use date arithmetic on a numeric field. The system does not know it's a date.

Try

SELECT     *
FROM         TESTSQL.C27AUDIT
WHERE     date(TESTSQL.C27AUDIT.C0027_DATE) >= CURRENT (DATE) - 5

Feles mala! Cur cista non uteris? Stramentum novum in ea posui!

 

[lkmalee]

We use ASC's SEQUEL product for SQL queries so the syntax and abilities are similar but probaly not the same. In SEQUEL, this would cause a problem because you are trying to compare a date data type to a numeric data type. You need to find a way to convert one or the other so the data types match. The dates in our system are eight-digit numerics. I have found that using a function to convert the current date to a numeric is 'safer' than converting the numeric field to a date. Converting the numeric field to a date data type requires that every record contains a valid date. Zeros or all 9's for example will be rejected as an invalid date error. On the flip side, the converted current date would have to be formatted to match the format your numeric dates have (YYMMDD or YYYYMMDD or MMDDYY, etc.). Hope this helps.

 

[mistektips]

Hi lkmalee
I agree with you that one of the format needs to be converted because the two operands that are compared are in different formats. But I am not sure how to convert the current date to numeric. Basically it has to changed to this format:yyyymmdd.
Any help will be appreciated.

 

[lkmalee]

I assume by now you've found the response by flapeyre. Based on my limited knowledge of your version of SQL, it looks like that may the answer you are looking for. That is, taking into consideration any invalid dates values in your field.

 

[flapeyre]

You may have to cast the CURRENT(DATE) -5 as a numeric. Ypu can't cast a date as numeric (directly).

Feles mala! Cur cista non uteris? Stramentum novum in ea posui!

 

[mistektips]

I tried what flapeyre suggested but I still keep getting errors. This is the error message I get:
TOKEN WAS NOT VALID
VALID TOKEN: DATA PTH TIME SQLID SCHEMA FUNCTION TIMESTAMP <IDENTIFIER>

 

[lkmalee]

I tried your SQL in the Run SQL Scripts in Operation Navigator and I got the same error message. Some research showed that the syntax for current date is incorrect.
Try: char(CURRENT DATE - 5 DAYS)
The DAYS notation is required.
To your original question: I have found that there is no easy way to compare a numeric field containing dates to a date data type. It appears you cannot cast a date directly into an integer. Here is code that should work:

SELECT    *
FROM      TESTSQL.C27AUDIT
WHERE     (substr(char(TESTSQL.C27AUDIT.C0027_DATE),1,4) || '-' || substr(char(TESTSQL.C27AUDIT.C0027_DATE),5,2) || '-' || substr(char(TESTSQL.C27AUDIT.C0027_DATE),7,2)) > char(CURRENT DATE - 5 days);

To make it work, I have converted both sides of the query into character data.
Your date field is assumed to be in yyyymmdd format. If it is not, you will need to modify the SUBSTR() functions accordingly.