IsValid for Time

altruist · Post by **altruist** » Fri May 02, 2014 5:42 pm

Hi,

How can we check the validity of the below fields coming in as string from source

Timestamp  --- IsValid('TIMESTAMP',InputField)
Timestamp (Microseconds)
Time
Time (Microseconds)

Thanks & Regards

chulett · Post by **chulett** » Fri May 02, 2014 6:59 pm

Check the documentation for that IsValid function. You would use a type of "timestamp" for the first two and "time" for the second two. And then the format string you would craft from the options noted here based on what your incoming string looks like.

Have you tried using it yet? Had any issues? If so please post both your input strings and your syntax with the results and we'll see if we can point you in the right direction.

ssnegi · Post by **ssnegi** » Fri May 02, 2014 10:17 pm

IsValid('Timestamp',DSLINK.INPUT,'%yyyy-%mm-%dd %hh:%nn:%ss.6')
IsValid('Time',DSLINK.INPUT,'%hh:%nn:%ss')
IsValid('Time',DSLINK.INPUT,'%hh:%nn:%ss.6')

You can change the format based on the input string

altruist · Post by **altruist** » Mon May 05, 2014 6:29 am

IsValid is not taking three arguments. I am using Datastage 8.1

chulett · Post by **chulett** » Mon May 05, 2014 7:18 am

What syntax does your version of the documentation show? I have 8.5 but not 8.1 handy.

ssnegi · Post by **ssnegi** » Mon May 05, 2014 5:26 pm

Code: Select all

if IsValid('Timestamp',field(DSLink.INPUT,'.',1)) and IsValid('int64',field(DSLink.INPUT,'.',2)) and len(field(DSLink.INPUT,'.',2)) <= 6 then @TRUE else @FALSE

IsValid('Time',DSLINK.INPUT) 

if IsValid('Time',field(DSLink.INPUT,'.',1)) and IsValid('int64',field(DSLink.INPUT,'.',2)) and len(field(DSLink.INPUT,'.',2)) <= 6 then @TRUE else @FALSE

Here I am assuming the the micro seconds are delimited from the timestamp by a '.' You can modify the field function depending on the delimiter.

altruist · Post by **altruist** » Mon May 05, 2014 9:26 pm

This is what I found in the documentation for IsValid

Description:Return whether the given string is valid for the given type. Valid types are ″date″, ″decimal″, ″dfloat″, ″sfloat″, ″int8″, ″uint8″, ″int16″, ″uint16″, ″int32″, ″uint32″, ″int64″, ″uint64″, ″raw″, ″string″, ″time″, ″timestamp″. ″ustring″
Arguments
type (string) format (string)
Output
result (int8)

ssnegi · Post by **ssnegi** » Mon May 05, 2014 9:47 pm

In version 8.7 the IsValid function is of the type :

Code: Select all

IsValid(%typestring%,%valuestring%,[%format%])

This doesn't seem to be case in earlier version. So you can use the workaround code that I published earlier.

altruist · Post by **altruist** » Mon May 05, 2014 11:28 pm

ssnegi wrote:
Code: Select all
if IsValid('Timestamp',field(DSLink.INPUT,'.',1)) and IsValid('int64',field(DSLink.INPUT,'.',2)) and len(field(DSLink.INPUT,'.',2)) <= 6 then @TRUE else @FALSE

IsValid('Time',DSLINK.INPUT) 

if IsValid('Time',field(DSLink.INPUT,'.',1)) and IsValid('int64',field(DSLink.INPUT,'.',2)) and len(field(DSLink.INPUT,'.',2)) <= 6 then @TRUE else @FALSE
Here I am assuming the the micro seconds are delimited from the timestamp by a '.' You can modify the field function depending on the delimiter.

While using the above syntax... it is not accepting format such as %mm/%dd/%yyyy %hh:%nn:%ss.6

ssnegi · Post by **ssnegi** » Tue May 06, 2014 12:19 am

convert the string %mm/%dd/%yyyy %hh:%nn:%ss.6
to desired format %yyyy-%mm-%dd %hh:%nn:%ss.6
using this derivation in stage variable:

Code: Select all

sv1 : 
DSLink.INPUT[7,4]:'-':DSLink.INPUT[1,2]:'-':DSLink.INPUT[4,2]:' ':field(DSLink.INPUT,' ',2)

Then apply IsValid Derivation in subsequent variable.

Code: Select all

sv2 :
If IsValid('Timestamp',field(sv1,'.',1)) and IsValid('int64',field(sv1,'.',2)) and len(field(sv1,'.',2)) <= 6 then @TRUE else @FALSE