IsValid and StringToDate
Moderators: chulett, rschirm, roy
IsValid and StringToDate
My incoming column has data such as "19950301" and it is char(10). I am trying to do:
StringToDate(IncomingCol, "%yyyy-%mm-%dd") (this by ltself returns all *)
and then I am trying to do:
Isvalid("date",StringToDate(IncomingCol, "%yyyy-%mm-%dd"))
I also tried:
Isvalid(StringToDate(IncomingCol, "%yyyy-%mm-%dd"), "%yyyy-%mm-%dd")
and
Isvalid(IncomingCol, "%yyyy-%mm-%dd")
All have failed. I have an IF condition which displays a value if IsValid of the date returns true and every time the column is empty. I have pretty much tried everything in this forum. Any ideas?
StringToDate(IncomingCol, "%yyyy-%mm-%dd") (this by ltself returns all *)
and then I am trying to do:
Isvalid("date",StringToDate(IncomingCol, "%yyyy-%mm-%dd"))
I also tried:
Isvalid(StringToDate(IncomingCol, "%yyyy-%mm-%dd"), "%yyyy-%mm-%dd")
and
Isvalid(IncomingCol, "%yyyy-%mm-%dd")
All have failed. I have an IF condition which displays a value if IsValid of the date returns true and every time the column is empty. I have pretty much tried everything in this forum. Any ideas?
Try this.
Code: Select all
IsValid('date',StringToDate(inCol,'%yyyy%mm%dd'))
As balajisr implies in his example - you cannot include dashes in your date mask when your source data is not, in fact, delimited with dashes as well.StringToDate(IncomingCol, "%yyyy-%mm-%dd")
Code: Select all
StringToDate(IncomingCol,"%yyyy%mm%dd")
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
according to me,datastage manual has some ambiguity here describing StringToDate:Returns a date from the given string in the given format,in fact,the given format is for the incoming string,so you shouldn't use "%yyyy-%mm-%dd",instead,"%yyyy%mm%dd",of course ,if it's 03011995 in source string,then use "%mm%dd%yyyy"
MaDFroG
This is your output representation for an invalid date format.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The date format string must match the date format.
"%yyyy-%mm-%dd" matches "1995-03-01" but does not match "19950301".
"%yyyy%mm%dd" matches "19950301" but does not match "1995-03-01".
"%yyyy-%mm-%dd" matches "1995-03-01" but does not match "19950301".
"%yyyy%mm%dd" matches "19950301" but does not match "1995-03-01".
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 247
- Joined: Thu Apr 27, 2006 6:38 am
- Location: Hyderabad