DSXchange

Hi,

I'm loading data from a sequential file into a table. There is one Date column which has to be validated before loading.
I need to check if the value present in that column in the .csv file is a valid date.
Are anyone aware of any inbuilt validations present in datastage? Or else, can someone please advice me on how to validate this in the Transformer stage ( so that the invalid rows can be dropped).

There is no built in function to check the validity of dates in Server jobs (parallel job have the IsValid function).

But you can use the Oconv/Iconv functions for validating the incoming date.

One way to go about this would be -
This first converts the incoming date to the DataStage internal format, and then reconverts it to the external format. If the incoming date was valid then the reconverted external date value would equal the incoming date value.

Aneesh

I need to check if the value present in that column in the .csv file is a valid date.
Are anyone aware of any inbuilt validations present in datastage?

Add this in your transformer constraint - Oconv(Oconv(Iconv(Input.date,"DYMD"),"DYMD"),"MCN")= Input.date - This filters out invalid dates (will check for the length 8 also)

Unfortunately the ICONV() and OCONV() functions will return the original string, so the result of the 2 functions on an invalid string will be the original string - just as if it were a valid date. The correct way to do this is to use a ICONV() function and check the value of STATUS(); but as that cannot be done without resorting to a user-written routine the simple method of a single ICONV() and comparing that to a string should be sufficient, although it will return a valid conversion for some dates that might not be valid, such as in leap years.

Ray has a Server version of the IsValid function available for download from his site from what I recall.

I think below checking should work
Code:

Oconv(Oconv(Iconv(Input.Date,"D-YMD[4,2,2]"), "D-YMD[4,2,2]"),"MCN") = Input.Date


For example:
Quote:

For date --> 20070704
Iconv(Input.DT1,"D-YMD[4,2,2]") --> 14430
Oconv(Iconv(Input.DT1,"D-YMD[4,2,2]"), "D-YMD[4,2,2]") --> 2007-07-04
Oconv(Oconv(Iconv(Input.DT1,"D-YMD[4,2,2]"), "D-YMD[4,2,2]"),"MCN") --> 20070704


For below set of input records:

NULL
20070704
2000704
2007070
20070000
20077704
20070744
00070704
00000704

Output will be only :

20070704
00070704

Which is right I believe.
.

Joshy - the output is right, but the overhead of 3 I/Oconv call is to much, just a simple "Iconv(Input.Date,"D-YMD[4,2,2]")=Input.Date" is sufficient.

ArndW - Iconv(Input.Date,"D-YMD[4,2,2]") will be Julian date and won't be equal to Input.Date. You won't get any output records.

If ICONV() of a string is equal to the string then you have an invalid date.

ICONV() of a string will not be equal to the orginal string. Did you mean to say something else?

ArndW wrote:If ICONV() of a string is equal to the string then you have an invalid date. ...

ICONV('THIS IS A BAD DATE','D4/E') is equal to 'THIS IS A BAD DATE'. Therefore just 1 ICONV() call is necessary to determine that you have an invalid date format, it is not necessary to issue 3 calls to do this.

Inline you can see:
For example:

For date --> 20070704
Iconv(Input.Date,"D-YMD[4,2,2]") --> 14430

I have tested it with just Iconv() and have posted the result in my previous post. No output records.

This should work

Thnak you very much for all the responses.
I will try it out and update the post.

Thank you very much for all the response. I shall try it out and update the post.