Solved

Custom DB2 For iSeries UDF To Convert To Date

Posted on 2013-06-25
17
1,505 Views
Last Modified: 2013-07-01
Some programmer way back when stored dates our database in MMDDYY format in a numeric field. I'm trying to write a UDF to convert this into a date type field. This is the code that I'm using ...

create function drdata.mmddyy(mydate decimal(6,0))
                returns DATE
                language sql
                return CAST(left('20' || right(mydate,2) ||  '-' || right('0' || trim(mydate),6),2) || '-' || substr(right('0' || trim(mydate),6),3,2) as DATE);
;

Open in new window


The function generates just fine. However, when using it the function in an SQL statement, I recieve the following error ...

SQL State: 22007
Vendor Code: -180
Message: [SQL0180] Syntax of date, time, or timestamp value not valid. Cause . . . . . :   The string representation of a date, time, or timestamp value does not conform to the syntax for the specified or implied data type and format. SQL_RETURN.SQLF_OUTPUT is either the character string constant that is not valid or the column or host variable that contained the string. If the name is *N, then the value is an expression specified in the statement. If the string was found in a host variable, the host variable number is 1. Recovery  . . . :   Ensure that the date, time, or timestamp value conforms to the syntax for the data type it represents. Try the request again.

So, the question is, then, how do I figure out what the proper string representation that needs to be crafted?

I've queried for the system date and it comes in YYYY-MM-DD format. That is what the example function currently puts out and results in the error. I've tried every other format I can think of to no avail.

Can anyone tell me how to determine what format it's looking for? Or, maybe there is something wrong with my cast statement that is causing this?
0
Comment
Question by:qholmberg
  • 7
  • 4
  • 3
  • +1
17 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 39276213
Hi qholmberg,


create function drdata.mmddyy(mydate decimal(6,0))
                returns DATE
                language sql
                return CAST(
  left('20' || right(mydate,2) ||  '-' ||
  right('0' || trim(mydate),6),2) || '-' ||
  substr(right('0' || trim(mydate),6),3,2) as DATE);

       
It looks like the function isn't producing the correct output.  I've split the function into multiple lines and the left() function is being applied over what should be the year and month.  Try this:

create function drdata.mmddyy(mydate decimal(6,0))
                returns DATE
                language sql
                return CAST('20' || right(mydate,2) ||  '-' || right('0' || trim(left (mydate, 2)),2) || '-' || right ('0' || trim (substr(mydate,4,2)), 2) as DATE);
;
                                 
Kent
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39276230
Ok.  Small typo on my part.  :)

Here's a CTE that proves this version.

with asdf (mydate) as (values '021496')
select '20' || right(mydate,2) ||  '-' || right('0' || trim(left (mydate, 2)),2) || '-' || right ('0' || trim (substr(mydate,3,2)), 2)  from asdf

Note that it is not year 2000 tolerant.  You may wish to include a CASE operation to us '20' when the year is less than 20 (or <= current year) and '19' otherwise.


Kent
0
 
LVL 18

Accepted Solution

by:
daveslash earned 500 total points
ID: 39276238
This translation should do it. Just wrap it in a function:

SELECT
       date('20' || right(trim(varchar(ANUMERICDATE)),2) || '-' ||
       left(lpad(trim(varchar(ANUMERICDATE)),6,'0'),2) || '-' ||  
       substr(lpad(trim(varchar(ANUMERICDATE)),6,'0'),3,2))      
  FROM deleteme

HTH,
DaveSlash
0
 
LVL 18

Expert Comment

by:daveslash
ID: 39276274
This should do it:

CREATE FUNCTION ConvertDate (
    aNumericDate numeric(6,0)
)
  RETURNS DATE
  LANGUAGE SQL
  modifies sql data

BEGIN

DECLARE outputDate DATE;

SET outputDate = 
    date('20' || right(trim(varchar(aNumericDate)),2) || '-' ||
    left(lpad(trim(varchar(aNumericDate)),6,'0'),2) || '-' ||  
     substr(lpad(trim(varchar(aNumericDate)),6,'0'),3,2));

return outputDate;

END

Open in new window


HTH,
DaveSlash
0
 
LVL 18

Expert Comment

by:daveslash
ID: 39276277
select aNumericDate,            
       convertDate(aNumericDate)

ANUMERICDATE  CONVERTDATE
    62,513    2013-06-25 
   122,513    2013-12-25 
    10,113    2013-01-01 

Open in new window

0
 

Author Comment

by:qholmberg
ID: 39279133
Thank you for your help, guys.

It was true that my string concatination was incorrect which was a result of moveing things around in vein attemtps to find the right combination and not putting it back correclty before posting.

The varchar seemed to be the key to returning the correct format, though. Without it, even the correct concatination would not work.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39279173
You were getting the commas from an edited data in the converted date string?  I certainly wouldn't have expected that!
0
 
LVL 27

Expert Comment

by:tliotta
ID: 39283278
Note that the expression might be logically simplified:
cast(
 '20' || right(digits(mydate),2) ||
 '-' ||
   left(digits(mydate),2) ||
 '-' ||
   substr(digits(mydate),3,2)
 as DATE)

Open in new window

The DIGITS() function can help a lot when leading zeros are an issue with numeric columns.

Tom
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 45

Expert Comment

by:Kdo
ID: 39284149
Hi Tom,

Based on the original query, I would expect that to fail as the month/day values may be a single digit with leading space.  The DIGITS function will squeeze out the spaces.


Kent
0
 

Author Comment

by:qholmberg
ID: 39284277
I've seen recommended and used the digits function to pad zeros in this manner. It does work. Actually, I took his example and plugged it into my function in an attempt to resolve another issue. It's output was perfect.

Unfortunately, my second issue persists. I think it's due to bad data.

Time to go learn DB2 UDF error catching ...
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39284379
There are several tests that you can run on the data to find the non-conforming data.  The TRANSLATE function is your friend here.

SELECT * FROM mytable
WHERE translate (some_column, '0123456789', ' ') <> ' ';

  or

SELECT * FROM mytable
WHERE translate (some_column, ' 123456789', '0000000000') <> '000000';


Starting with either of those will take you a long way.  Note that CHAR data is length specific so testing for a single space works only if the column is defined as CHAR(1).  Either cast to VARCHAR or include the correct number of spaces in the string.  In the second example, the comparison string must match the number of characters in the source column.


Kent
0
 

Author Comment

by:qholmberg
ID: 39284503
Looks interesting. Something is off, though. It seems to return everything. Here is the statement ...
select * 
from qs36f.jobmast
where translate(varchar(jbdat), '123456789', '000000000') <> '000000'

Open in new window


If I run it as such ...
select jjob, jbdat, translate(varchar(jbdat), '1234567889', '000000000')
from qs36f.jobmast
where translate(varchar(jbdat), '123456789', '000000000') <> '000000'

Open in new window

It shows me that the jbdat column and the translated jbdat column are the same except the translated column is left justified indicating it is now a char field.

It does not seem that the translate actually happened.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39284533
Grrr.....   Dyslexia at work.  Apologies.....  The 2nd and 3rd parameters are reversed.


select jjob, jbdat, translate(varchar(jbdat), '000000000', '123456789')
from qs36f.jobmast
where translate(varchar(jbdat), '000000000', '123456789') <> '000000'
                                           

Kent
0
 
LVL 27

Expert Comment

by:tliotta
ID: 39287632
Hi, Kent,

Based on the original query, wouldn't it be difficult for a [ decimal(6,0) ] data type parameter to have any spaces at all? There should never be a space in the parameter value, and DIGITS() should guarantee that the function result has none. Leading zeros should be preserved.

...unless this isn't really DB2 for i.

Tom
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39289857
Hi Tom,

No argument here.  :)  

Even though his UDF declared a parameter of DECIMAL(6, 0), the OP was going through the standard steps to ensure leading zeros as if each "token" in the packed MMDDYY field could contain a leading space.  I took that to mean that he had seen that in the data and was experimenting with ways to resolve it.  Passing a parameter such as '12 511' to his UDF may throw an exception.  I don't know if DB2 for iSeries would throw it on the function call or when the UDF tries to use it as a numeric.

I guess I just glossed over the UDF declaration and went right to the body, saw the hoops that he was jumping through, and approached it from purely a string standpoint.


Kent
0
 

Author Comment

by:qholmberg
ID: 39290284
The original data is from a field of numeric type representing a date in MMDDYY format. Therefore, the data could be either a 5 digit number or a 6 digit number depending on whether the MM was 1-9 or 10 - 12 respectively. As I'm sure you know, a DBMS won't hold the leading 0 in a numeric field. Therefore, if I am to slice the first 2 to be used as MM or substing the middel 2 to be used as DD, padding that leading zero is one method for ensuring I get the right strings.

While I've been writing select statements for over a decade, this is the first UDF I've written. All the hoops I've jumped through are from taking what I've done in a select statements in order to slice the data up into parts and, simultaneoulsly, reassembe it into a format that the end user would understand. I just took that and decided to utilize it in a UDF to A) make my select code simpler and B) be able to use the date in the where clause.

Now that I've written my first UDF and gained a basic understanding, I can already see much more efficient methods for going about this utilizing variables and the additional programming logic availalbe in a UDF that are not available to me in a standard select statement.

Thank you all for helping me get off the ground with UDFs.
0
 
LVL 27

Expert Comment

by:tliotta
ID: 39292147
As I'm sure you know, a DBMS won't hold the leading 0 in a numeric field.
Commented for pure technical purposes only:

As you should learn for technical detail, yes, it will.

It will ensure that leading zeros exist. In DB2 for i (and I would assume for all other forms of DB2, and probably for most other DBMSs), it will not allow numeric values to be written into the database without leading zeros (i.e., when zeros should be in the leading positions). You'll get a database 'write' error if you somehow manage to cause leading zeros to be removed at the moment the physical write happens.

However, that is based on the "numeric field" part of your statement. When some function converts the content of a numeric field to a human-readable character form, that's when leading zeros become superfluous and generally suppressed. As long as you stay within the DBMS query language, you generally are never allowed to see what the DBMS actually stores or retrieves.

The SQL DIGITS() function allows you to do the conversion to a character form while retaining the 'zero' digits.

Tom
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

705 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

13 Experts available now in Live!

Get 1:1 Help Now