Valiadete date field
Moderators: chulett, rschirm, roy
Valiadete date field
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
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
Create and use a Routine
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.
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.
Code: Select all
BadInternalDate = ICONV(In.Column,'D MDY')
Month = OCONV(BadInternalDate,'DM')
Day = OCONV(BadInternalDate,'DD')
Year = OCONV(BadInternalDate,'DY') + EraStart
CorrectDate = FMT(Month,"2'0'R"):" ":FMT(Day,"2'0'R"):" ":FMT(Year,"4'0'R")
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Search the forum for a server version of the IsValid() function.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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.
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)
~Kris
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Use the function to determine that the data is valid, and to determine that it matches your required pattern.
Code: Select all
InLink.TheString Matches "2N'/'2N'/'4N"
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.