DSXchange: DataStage and IBM Websphere Data Integration Forum
  Home  •  Forum  •  Search  •  Favorites  •  Register  •  Log in
Usergroups  •  Contact  •  FAQ  •  Privacy Policy  •  Log in to check your private messages
Date Conversion
 View next topic
View previous topic
Add To Favorites
Post new topic   Reply to topic    DSXchange Forum Index » IBM® DataStage Enterprise Edition (Formerly Parallel Extender/PX)
Author Message
manojbh31



Group memberships:
Premium Members

Joined: 21 Jun 2007
Posts: 83

Points: 711
Post Posted: Wed Dec 06, 2017 7:14 am Reply with quote    Back to top    
DataStage® Release: 9x
Job Type: Parallel
OS: Unix
Hi All,

I have a column in DB which is integer and has date values as yymmdd and ymmdd, I need to compare whether it is valid date format in datastage.
View user's profile  Send private message    
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42622
Location: Denver, CO
Points: 219444
Post Posted: Wed Dec 06, 2017 7:37 am Reply with quote    Back to top    
Well, DecimalToString() and IsValid() with Type 'date' come to mind. What have you tried?

_________________
-craig

And I'm hovering like a fly, waiting for the windshield on the freeway...
View user's profile  Send private message    
Rate this response: 0  1  2  3  4  5   
Not yet rated
sriven786
Participant



Joined: 08 Nov 2017
Posts: 35

Points: 260
Post Posted: Wed Dec 06, 2017 8:40 am Reply with quote    Back to top    
Similar post on Date Validation, Please take a look and try

http://www.dsxchange.com/viewtopic.php?p=256967

_________________
Venkata Srini
View user's profile  Send private message    
Rate this response: 0  1  2  3  4  5   
Not yet rated
jackson.eyton



Group memberships:
Premium Members

Joined: 26 Oct 2017
Posts: 109

Points: 1819
Post Posted: Wed Dec 06, 2017 3:47 pm Reply with quote    Back to top    
Another similar post:
http://www.dsxchange.com/viewtopic.php?p=478727&highlight=#478727

Code:
----- VERSION 6 -----
--Required XFM Variables--
CurrentYear = YearFromDate(CurrentDate())
--- (C) MMDDYYYY Optimized ---
IF $1 <> 0
THEN (
   IF (IsValid("date",Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),8),"%mm%dd%yyyy") AND (Abs(YearFromDate(StringToDate(Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),8),"%mm%dd%yyyy")) - CurrentYear) < 100))
   THEN StringToDate(Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),8),"%mm%dd%yyyy")
   ELSE (
      IF (IsValid("date",Right(Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),7),"%yyyy%ddd") AND (Abs(YearFromDate(StringToDate(Right(Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),7),"%yyyy%ddd")) - CurrentYear) < 100))
      THEN StringToDate(Right(Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),7),"%yyyy%ddd")
      ELSE (
         IF (IsValid("date",Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),6),"%mm%dd%1950yy") AND (Abs(yearFromDate(StringToDate(Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),6),"%mm%dd%1950yy")) - CurrentYear) < 100))
         THEN StringToDate(Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),6),"%mm%dd%1950yy")
         ELSE SetNull()
         )
      )
   )
ELSE SetNull()

--Single Line Format--
IF $1 <> 0 THEN (IF (IsValid("date",Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),8),"%mm%dd%yyyy") AND (Abs(YearFromDate(StringToDate(Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),8),"%mm%dd%yyyy")) - CurrentYear) < 100)) THEN StringToDate(Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),8),"%mm%dd%yyyy") ELSE (IF (IsValid("date",Right(Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),7),"%yyyy%ddd") AND (Abs(YearFromDate(StringToDate(Right(Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),7),"%yyyy%ddd")) - CurrentYear) < 100)) THEN StringToDate(Right(Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),7),"%yyyy%ddd") ELSE (IF (IsValid("date",Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),6),"%mm%dd%1950yy") AND (Abs(yearFromDate(StringToDate(Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),6),"%mm%dd%1950yy")) - CurrentYear) < 100)) THEN StringToDate(Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),6),"%mm%dd%1950yy") ELSE SetNull()))) ELSE SetNull()






--- (J) YYYYDDD Optimized ---
IF $1 <> 0
THEN (
   IF (IsValid("date",Right(Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),7),"%yyyy%ddd") AND (Abs(YearFromDate(StringToDate(Right(Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),7),"%yyyy%ddd")) - CurrentYear) < 100))
   THEN StringToDate(Right(Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),7),"%yyyy%ddd")
   ELSE (
      IF (IsValid("date",Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),6),"%mm%dd%1950yy") AND (Abs(yearFromDate(StringToDate(Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),6),"%mm%dd%1950yy")) - CurrentYear) < 100))
      THEN StringToDate(Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),6),"%mm%dd%1950yy")
      ELSE (
         IF (IsValid("date",Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),8),"%mm%dd%yyyy") AND (Abs(YearFromDate(StringToDate(Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),8),"%mm%dd%yyyy")) - CurrentYear) < 100))
         THEN StringToDate(Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),8),"%mm%dd%yyyy")
         ELSE SetNull()
         )
      )
   )
ELSE SetNull()

--Single Line Format--
IF $1 <> 0 THEN (IF (IsValid("date",Right(Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),7),"%yyyy%ddd") AND (Abs(YearFromDate(StringToDate(Right(Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),7),"%yyyy%ddd")) - CurrentYear) < 100)) THEN StringToDate(Right(Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),7),"%yyyy%ddd") ELSE (IF (IsValid("date",Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),6),"%mm%dd%1950yy") AND (Abs(yearFromDate(StringToDate(Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),6),"%mm%dd%1950yy")) - CurrentYear) < 100)) THEN StringToDate(Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),6),"%mm%dd%1950yy") ELSE (IF (IsValid("date",Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),8),"%mm%dd%yyyy") AND (Abs(YearFromDate(StringToDate(Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),8),"%mm%dd%yyyy")) - CurrentYear) < 100)) THEN StringToDate(Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),8),"%mm%dd%yyyy") ELSE SetNull()))) ELSE SetNull()






-- (C) MMDD1950YY Optimized --
IF $1 <> 0
THEN (
   IF (IsValid("date",Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),6),"%mm%dd%1950yy") AND (Abs(yearFromDate(StringToDate(Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),6),"%mm%dd%1950yy")) - CurrentYear) < 100))
   THEN StringToDate(Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),6),"%mm%dd%1950yy")
   ELSE (
      IF (IsValid("date",Right(Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),7),"%yyyy%ddd") AND (Abs(YearFromDate(StringToDate(Right(Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),7),"%yyyy%ddd")) - CurrentYear) < 100))
      THEN StringToDate(Right(Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),7),"%yyyy%ddd")
      ELSE (
         IF (IsValid("date",Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),8),"%mm%dd%yyyy") AND (Abs(YearFromDate(StringToDate(Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),8),"%mm%dd%yyyy")) - CurrentYear) < 100))
         THEN StringToDate(Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),8),"%mm%dd%yyyy")
         ELSE SetNull()
         )
      )
   )
ELSE SetNull()

--Single Line Format--
IF $1 <> 0 THEN (IF (IsValid("date",Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),6),"%mm%dd%1950yy") AND (Abs(yearFromDate(StringToDate(Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),6),"%mm%dd%1950yy")) - CurrentYear) < 100)) THEN StringToDate(Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),6),"%mm%dd%1950yy") ELSE (IF (IsValid("date",Right(Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),7),"%yyyy%ddd") AND (Abs(YearFromDate(StringToDate(Right(Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),7),"%yyyy%ddd")) - CurrentYear) < 100)) THEN StringToDate(Right(Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),7),"%yyyy%ddd") ELSE (IF (IsValid("date",Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),8),"%mm%dd%yyyy") AND (Abs(YearFromDate(StringToDate(Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),8),"%mm%dd%yyyy")) - CurrentYear) < 100)) THEN StringToDate(Right("0":Change(Trim(DecimalToString($1,"suppress_zero")," ","B"),".",""),8),"%mm%dd%yyyy") ELSE SetNull()))) ELSE SetNull()


This is the series of derivations that I use depending on the columns assigned date format. In some cases the values were entered incorrectly entirely or in a format that the column description does not specify. Ensuring I use the derivation that STARTS with the columns assigned format optimizes the conversion process and job speed. Remember that $1 is the derivation substitution variable for whatever is already in the derivation, so I link up the columns in the transform stage then run a derivation substitution (right click menu) and paste in the appropriate derivation. I've yet determined a good way to wrap this all into a shared container that wouldn't require just as much work to utilize via RCP and column renaming than the derivation substitution already does.

_________________
-Me
View user's profile  Send private message    
Rate this response: 0  1  2  3  4  5   
Not yet rated
Display posts from previous:       
Post new topic   Reply to topic    DSXchange Forum Index » IBM® DataStage Enterprise Edition (Formerly Parallel Extender/PX)


 Jump to:   



Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours