Hi All

I am trying to write a query to only return rows between dynamic dates. However the date im using is actually a char. If I put DATE() around the char it returns '++++++++++++++'

Could this be to do with the characters set being different on the server than the database?

Any suggestions appreciated
Thanks in advance

Casting a CHAR-string to a DATE will never return '++++++++++++++' in DB2. So it would be helpful if you could tell us which system you are using in which version and what the actual SQL statements are.

I am using V5R1M0. A test I done on the char to see the result was:
SELECT DATE(LOGDAT) FROM QPRLOG this returned '++++++++++++++'

The full query im trying to write is:
SELECT LOGLND, LOGTRN, LOGRQS, LOGDAT FROM QPRLOG WHERE LOGRQS = '001' AND DATE (days (LOGDAT)) - days (current date - 7 days) and (days (LOGDAT)) - days (current date - 1 days)

Thanks

There were syntax errors.
What is your requirement with this predicates?
Is that something like this?
"LOGDAT is between current date - 7 days and current date - 1 day"
(This is not right SQL syntax. I'm asking your intention.)

Hi Tonkuma,

Im trying to see all rows for the last 7 days.

Thanks

I've just noticed I pasted the wrong query it should have been:

SELECT DATE(LOGDAT) FROM QPRLOG WHERE LOGRQS = '001' AND DATE(LOGDAT) BETWEEN days (current date - 7 days) AND days (current date - 1 days)

The problem is that DATE(LOGDAT) returns '++++++++++++' and is not converted to a date but im not sure why this happens

What are actual data of LOGDAT?
Could you provide the result of ...
SELECT LOGDAT FROM QPRLOG FETCH FIRST 10 ROWS ONLY

Please try...
SELECT LOGLND, LOGTRN, LOGRQS, LOGDAT FROM QPRLOG WHERE LOGRQS = '001' AND DATE(LOGDAT) BETWEEN (current date - 7 days) AND (current date - 1 day)
"DATE(LOGDAT)" may be required to change.

thanks for all your help I finally got it to work:

Select DATE(SUBSTR(LOGDAT,1,4)||'-'||
SUBSTR(LOGDAT,5,2)||'-'||
SUBSTR(LOGDAT,7,2)
)

From QPRLOG
WHERE LOGRQS = '001'
AND DATE(SUBSTR(LOGDAT,1,4)||'-'||
SUBSTR(LOGDAT,5,2)||'-'||
SUBSTR(LOGDAT,7,2)
) BETWEEN current date - 7 days AND current date

Really don't know why DATE() didn't work but this solution is fine.
Again thanks for your time.
Sinead

Looking into your code, I guessed that the format of LOGDAT is 'yyyymmdd'.
The format is not supported as string representation of date data.

Please see the manual "DB2 Universal Database for iSeries SQL Reference Version 5".
Chapter 2. Language Elements ---> Data Types ---> Datetime Values ---> String Representations of Datetime Values

yes that is the format, so thats it so. Thanks