Solved

AS400 SQL Cast Issue on Current Date/Time

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

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 …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

773 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