DSXchange

A short text to describe your forum
http://dsxchange.com/

Date Conversion

http://dsxchange.com/viewtopic.php?f=8&t=157676

Page 1 of 1

Date Conversion

Posted: Wed Dec 06, 2017 7:14 am
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.

Posted: Wed Dec 06, 2017 7:37 am
by chulett
Well, DecimalToString() and IsValid() with Type 'date' come to mind. What have you tried?

Re: Date Conversion

Posted: Wed Dec 06, 2017 8:40 am
by sriven786
Similar post on Date Validation, Please take a look and try

viewtopic.php?p=256967

Posted: Wed Dec 06, 2017 3:47 pm
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.
All times are UTC-06:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited