Thanks Dan!

That's a good idea, I have modified the code to have split date as 50 .
as in our data base we have data back since 1950's. . Well I guess I
have to just deal with it after 42 years. :-)

But still that looks odd that DB2 cannot covert yymmdd to date !


-Raj

-----Original Message-----
From: DanLuchinski
[mailto:db2udbdba-ezmlmshield-x1360588.[Email address protected]
Sent: Thursday, February 28, 2008 1:55 PM
To: LazyDBA Discussion
Subject: Re: to Date conversion issue

You could put a case statement around it. For example, if YY > 70, then

use 19 otherwise use 20 in your translate.

case when left(date_closed,2) > 70 then translate
DATE(TRANSLATE('19CD-EF-GH',DIGITS(date_closed),'ABCDEFGH'))
else translate
DATE(TRANSLATE('20CD-EF-GH',DIGITS(date_closed),'ABCDEFGH')) end

or use a different number than 70. Depends what works for you.

Dan Luchinski
Database Administrator
ASPCA Knowledge Management Center
University of Illinois Allied Agency
Phone: 217.337.9777 Fax: 217.337.0599 Web: www.aspca.org

"Nambiar.Dheeraj "
<db2udbdba-ezmlmshield-x88831267.[Email address protected] wrote on
02/28/2008 12:50:06 PM:

>
> Hello gurus
>
> I am stuck with an date conversion issue, the field in the table is
> decimal(8) and it has date in yymmdd format(not YYYYMMDD) .
>
> As of now I am trying to use
>
> DATE(TRANSLATE('20CD-EF-GH',DIGITS(date_closed),'ABCDEFGH')) but this
> will make the date to start with year 2000.
>
> Example
>
> If date_closed = 980201
>
> DATE(TRANSLATE('20CD-EF-GH',DIGITS(date_closed),'ABCDEFGH')) will
> produce
>
> 02/01/2098
>
> Which is wrong .Can any one suggest a better and a correct way
>
>
> Thanks
> -Raj
>
>
>
> LEGAL DISCLAIMER
> The information transmitted is intended solely for the individual or
> entity to which it is addressed and may contain confidential and/or
> privileged material. Any review, retransmission, dissemination or
> other use of or taking action in reliance upon this information by
> persons or entities other than the intended recipient is prohibited.
> If you have received this email in error please contact the sender
> and delete the material from any computer.
>
> SunTrust and Seeing beyond money are federally registered service
> marks of SunTrust Banks, Inc.
> [ST:XCL]
>
>
>
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


DB2 & UDB email list listserv db2-l LazyDBA home page