IsValid and StringToDate

[abc123]

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?

[balajisr]

Try this.

Code: Select all

IsValid('date',StringToDate(inCol,'%yyyy%mm%dd'))

[chulett]

StringToDate(IncomingCol, "%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.

Code: Select all

StringToDate(IncomingCol,"%yyyy%mm%dd")

[Zhang Bo]

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"

[abc123]

balajisr, your solution worked. However, can you tell me why:

StringToDate(IncomingCol, "%yyyy-%mm-%dd")

returns all *

[ArndW]

This is your output representation for an invalid date format.

[ray.wurlod]

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".

[balajisr]

As Arnd and Ray mentioned your date in string format does not match your format string in StringToDate().

[devidotcom]

Nice post helped me resolve my issue