Date Conversion

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
manojbh31
Premium Member
Premium Member
Posts: 83
Joined: Thu Jun 21, 2007 6:41 am

Date Conversion

Post by manojbh31 »

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.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Well, DecimalToString() and IsValid() with Type 'date' come to mind. What have you tried?
-craig

"You can never have too many knives" -- Logan Nine Fingers
sriven786
Participant
Posts: 37
Joined: Wed Nov 08, 2017 1:36 pm

Re: Date Conversion

Post by sriven786 »

Similar post on Date Validation, Please take a look and try

viewtopic.php?p=256967
Venkata Srini
jackson.eyton
Premium Member
Premium Member
Posts: 145
Joined: Thu Oct 26, 2017 10:43 am

Post by jackson.eyton »

Another similar post:
viewtopic.php?p=478727&highlight=#478727

Code: Select all

----- 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
Post Reply