Solved

AS400 SQL Cast Issue on Current Date/Time

Posted on 2016-07-28
4
83 Views
Last Modified: 2016-07-29
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
0
Comment
Question by:lynn_harris
  • 2
4 Comments
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 100 total points
ID: 41734058
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
 

Author Comment

by:lynn_harris
ID: 41734091
Excellent article. Thank You, Paul.
0
 
LVL 27

Accepted Solution

by:
tliotta earned 400 total points
ID: 41734586
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
 

Author Closing Comment

by:lynn_harris
ID: 41734669
Thank you. That is exactly what the values where.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
A short article about problems I had with the new location API and permissions in Marshmallow
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question