Solved

AS400 SQL Cast Issue on Current Date/Time

Posted on 2016-07-28
4
66 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
Comment Utility
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
Comment Utility
Excellent article. Thank You, Paul.
0
 
LVL 27

Accepted Solution

by:
tliotta earned 400 total points
Comment Utility
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
Comment Utility
Thank you. That is exactly what the values where.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now