DSXchange

Hi,

I am getting timestamp string (varchar(17) ) with milliseconds.

Now using transformer i want to validate whether Timestamp is in correct format with correct value.

I am doing this:
If IsValid('TimeStamp', CL.crctn_dltn_ts) Then SetNull() Else "2010".

But its not validating milliseconds i.e it is returning null for all the below mentioned timestamp values.
Its only validating Timestamp upto 14 character and ignoring milliseconds part.
2010-12-21 10:10:10.123
2010-12-21 10:10:10 123
2010-12-21 10:10:10.abc
2010-12-21 10:10:10.123456

Please advise ASAP.

Regards
~Nirmal

Your timestamp string should be varchar(26) with milliseconds.
Convert Varchar to timestamp StringToTimestamp(i/p col,"%yyyy-%mm-%dd %hh:%nn:%ss.6")) and then apply the IsValid function

use three stage variable.
sv1: validate date
sv2: validate time
sv3: validate micro seconds.

The Timestamp do not get fit in varchar(17). Use proper length.

The logic is based on your test data to validate whether it is Timestamp or not.

IsValid('Timestamp',StringToTimestamp(Inputcol,'%yyyy-%mm-%dd %hh:%nn:%ss.3))

The point about IsValid() is that it tests a string. There's no point trying to apply StringToTimestamp() until and unless you're confident that the string contains a valid timestamp.