Date Validations in Datastage

[ray.wurlod]

How about the server IsValid() function?

[Rea_Antony]

Hi,

I have used the

If OConv(IConv(<inputDate>, <conversion code>), <conversion code>) <> inputDate then "invalid" else "valid".

to check validity...
But there is still one scenario that isn't covered. Once we had a job failure because the input date had the year part outside the SQL Server acceptable range of 1753 and 9999.
Is anyone aware of any inbuilt transform in Datastage to check whether the date is valid by checking if the date is btw 1 & 31, the month is btw 1 and 12 and the year is btw 1753 and 9999?
Thanks in advance.

[chulett]

Not that I am aware of, feel free to write one. The only part you are missing is the year range restriction and that shouldn't be hard to add to your checks.

[Rea_Antony]

The only part you are missing is the year range restriction and that shouldn't be hard to add to your checks.

Hi,
Does the above quote mean that the date and month validations are already available in datastage?

[chulett]

Yes - they are already handled by the IConv function when handling dates, as is the year - just not with the restricted range you posted. Add another bit of logic to say that the date is invalid if the year portion of the date is outside that range as well as what you are already doing.

[Rea_Antony]

I was checking the HELP on Iconv function:

Remarks
The Status function returns the result of the conversion as follows:

0 The conversion was successful.

1 The string was invalid. An empty string was returned, unless string was a null value when null was returned.

2 The conversion was invalid.

3 Successful conversion but the input data may be invalid, for example, a nonexistent date, such as 31 September.

But when I tried using the date 31/09/07, it came in the destination as Ist October 2007

[ArndW]

As I mentioned before in this thread, you would need to write a routine in order to use the STATUS() value for an ICONV() or OCONV() call.
Which format did you use on "31/09/07"?

[Rea_Antony]

I used
OCONV(ICONV(DSLink9.Field003,"D2-DMY[2,2,2]"),"D2-YMD[4,2,2]"):" 00:00:00.000"
for 31/09/07

[Rea_Antony]

Another approach i tried was to do the date validations in the csv file from where im laoding data... but the date validation only works if its an excel. The feature doesnt seem to be supported in csv files..

[JoshGeorge]

This is what you should be using if you are checking for dd/mm/yy format date:

Oconv(Iconv(Input.Date,"D/DMY[2,2,2]"), "D/DMY[2,2,2]") = Input.Date

Check out the date format difference/ [/code]

[ArndW]

using the format

OCONV(ICONV(DSLink9.Field003,"D2-DMY[2,2,2]"),"D2-YMD[4,2,2]"):" 00:00:00.000"

on "31/09/07" cannot result in an output of "Ist October 2007".

Go into the Manager and create a new server routine. Put in one line into the program

Code: Select all

Ans=ICONV(Arg1,"D2-YMD[4,2,2]")

and feed it your input string. You will see that your format is wrong, it should be "D2/DMY[2,2,2]". Once you have this function running so that it produces an integer number (the internal format), add another line to your test routine.

Code: Select all

Ans = OCNOV(Ans,"D/DMY[2,2,2]")

and see what you get.

[Rea_Antony]

I'm sorry. I meant it gave me 01/10/2007

[ArndW]

...You will see that your format is wrong, it should be "D2/DMY[2,2,2]"...

[Rea_Antony]

Can someone advice me on how Iconv function can be used to accept different date formats at a time. What i mean to say is that, my job accepts 1-Jun-06 and 21/1/07 and 2/7/2007. But it stumps upon encountering 2006-jun-04.
Is it possible to use Iconv function to handle different types of date formats simultaneously?

[ArndW]

When you specify a simple "D4" conversion code the routine will make a best guess as to the format, but it is very poor practice as the computer has no idea if "2007-04-08" means August 4 or April 8; thus you will certainly get invalid conversions.