Solved

AS400 SQL Cast Issue on Current Date/Time

Posted on 2016-07-28
4
72 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2008 R2 - Execution Plan 3 56
combine an MS SQL string in Idera DM 9 48
Base1 Encode/Decode 3 67
How to base a filter depending on fields contents? 15 44
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
A short article about problems I had with the new location API and permissions in Marshmallow
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 …

947 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

21 Experts available now in Live!

Get 1:1 Help Now