DSXchange

I have a sequential file,coming in with 10 columns,the 5,6,7,8 and 10 columns are date fields.All the columns 5,6,7,8 have date fields in the format of mm/dd/yyyy.But column 10 has date field coming as

Nov 30 200
Jan 01 175


So,Now i want to validate column 10 saying if date is not coming in as 'mm/dd/yyyy' than set it to Null.Can you explain me,how to do this.


Thanks

Why OCONV function is there for?

You can do something like ---
1. Build a Routine say, ValidateDate(Arg1) using Iconv and Status functions (you will get their syntax in the documentation). In the Routine call Iconv with the Argument as the input date and then call Status fucntion.
2. In Transformer where you are loading the Target table column for the date, just use a derivation like -
If (ValidateDate("<inp_date>")<>0) then NULL else <inp_date>.
The Routine would return 0 if the date format is correct and as desired or else would have statuses as 1,2 which signifies there has been a problem in date conversion using Iconv.

Let me know if we were able to build the Routine and use it.

What is the base year for the last format? You could use ICONV() to get the components, but need to know the offset.

Search the forum for a server version of the IsValid() function.

Searching with few key words will give you answers before you even need to think about posting the question here.

There are several date validation routines (with comments) floating around on this portal.

Here is one from Ray.

http://dsxchange.com/viewtopic.php?t=91 ... 994e2f502f

I am sorry,I had tried the below routine before posting this question

FUNCTION IsValidDate(TheDate, Format)
If UnAssigned(Format) Or IsNull(Format) Or UnAssigned(TheDate) Or IsNull(TheDate)
Then
Ans = @NULL
End
Else
Test = Iconv(TheDate, Format)
Ans = Not(Status())
End
RETURN(Ans)

But when i test the routine by passing 2 parameters
i.e Nov 20 200 and the format as "DMDY",it returns a 1 (which means it is a valid format).So my question was the date is a valid date ,but what i intend to test is the format should be mm/dd/yyyy.
Any suggestions on this.

Iconv(DateString, "D/MDY") would convert the date in both mm/dd/yy and mm/dd/yyyy format into internal format.

So pass the value of "D/MDY" for format when you call below routine. That will do.

Equate RoutineName To 'TestRoutine'
If UnAssigned(Format) Or IsNull(Format) Or UnAssigned(TheDate) Or IsNull(TheDate)
Then
Ans = @NULL
End
Else
Test = Iconv(TheDate, Format)
Ans = Not(Status())
End
RETURN(Ans)

As Suggested by Kris,i have passed the value 'D/MDY' for the parameter value Format and Date parameter as 'Nov 20 200' ,this gives an output of 1(which means the date is valid).
But my requirement is if the format of the date is other than mm/dd/yyyy than it should say,invalid date(i.e return 0).

'D/MDY[2,2,4]' should get you closer.

I Even tried D/MDY[2,2,4] in the format parameter.
But even that returns a value 1,When i pass Parameter for the TheDate field as 'Nov 20 200'.

why don't you try the IsValid() routine Ray had suggested in his post.

Use the function to determine that the data is valid, and to determine that it matches your required pattern.

Thanks alot Ray,that worked out absolutely perfect.