AS400 SQL Cast Issue on Current Date/Time

Hi,

I'm having an SQL CAST problem on the current date/time when I run a QRY in batch, but not interactively.  I know there are a number of ways I could get the date, but I hoping someone can tell me why this is failing.  

Thank you for your time. I just like to understand when something doesn't make sense.

Lynn
cast-question.txt
lynn_harrisAsked:
Who is Participating?
 
tliottaConnect With a Mentor Commented:
Since you're using the STRQMQRY command and CL, the above article might not help without hitting on the right experiment. I suspect that the problem might be a mismatch between your time format in interactive SQL and the one used by STRQMQRY in batch. Perhaps combined with the (undisclosed) data attributes of CHPCTM, it could result in the SQL0420 error you show.

To test this, create a QM query from this statement:
select current time from sysibm/sysdummy1

Open in new window

Then run the QM query both interactively and in batch. Compare the time that is output against the time value shown when you run the same statement in interactive SQL.

Perhaps most common is that you'll see a time such as "05:01:22" in interactive SQL, but it may be "05.01.22" out of QM query. If so, your REPLACE() function for CHPCTM won't find matching characters to replace. Depending on the data attributes of CHPCTM, the final string can have characters that aren't valid.
0
 
PortletPaulConnect With a Mentor freelancerCommented:
there's a useful reference on dates here:
http://www.ibm.com/developerworks/data/library/techarticle/0211yip/0211yip3.html

it shows use of a custom function, which perhaps you can't use as is, but it does suggest these which may prove helpful

select
       substr( digits (day(current timestamp)),9)
    ,  substr( digits (month(current timestamp)),9)
    , rtrim(char(year(current timestamp)))
    , substr( digits (hour(current timestamp)),9)
   ,  substr( digits (minute(current timestamp)),9)
from sysibm.sysdummy1
0
 
lynn_harrisAuthor Commented:
Excellent article. Thank You, Paul.
0
 
lynn_harrisAuthor Commented:
Thank you. That is exactly what the values where.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.