populating valid date
Moderators: chulett, rschirm, roy
populating valid date
i have a column xxxx it is of format yyyy/mm/dd i have to see whether it is a valid format or not and if it is valid i have to load to target? how can i see if it is valid or not
The ICONV(In.DateStringColumn,"D4/YMD") function will return a number if it can be converted to internal format or will return the original string if it cannot.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
String to DB2 Date format
I had once developed a Basic routine to check valid format and then convert from a string. This routine is for DB2...it takes any format and changes to DB2 format..you can change it for your needArndW wrote:The ICONV(In.DateStringColumn,"D4/YMD") function will return a number if it can be converted to internal format or will return the original string if it cannot.
Function :
Change the input date to DB2 format (MM/DD/YYYY)- also validate date
Parameters:
Arg1 = Input Date field(Varchar: Datatype)
Arg2 = Input Format field(Varchar: Datatype) - the format of the input date
Valid input: YYYYMMDD
YYYY/MM/DD
YYYY-MM-DD
MMDDYYYY
MM/DD/YYYY
MM-DD-YYYY
DDMMYYYY
DD-MM-YYYY
DD/MM/YYYY
Called by:
Transformer
Return Values:
DB2 format date (MM/DD/YYYY)
1 = If the Input Field is a invalid date
The routine body is:
Code: Select all
Input = TRIM(aINDATE)
Sformat = Upcase(TRIM(aFORMAT))
Error=1
Begin Case
*********************************************************
*If input is not blank or the format is incorrect *
*********************************************************
Case (Len(Input) = 0)
Error=1
*********************************************************
*If input is 8 character and the format is YYYYMMDD *
*********************************************************
Case (Len(Input)=8 AND (Sformat = "YYYYMMDD"))
NormDate1=Input
Convert=Iconv(NormDate1,"D-YMD[4,2,2]")
Error =0
*********************************************************
*If input is 10 character and the format is YYYY/MM/DD *
*********************************************************
Case (Len(Input)=10 AND (Sformat = "YYYY/MM/DD"))
SlashCharac1= Iconv(Input,"MC/N")
Begin Case
Case (SlashCharac1="//")
SlashDate1=LEFT(Input,4): RIGHT(LEFT(Input,7),2): RIGHT(Input,2)
Convert=Iconv(SlashDate1,"D-YMD[4,2,2]")
Error =0
Case (SlashCharac1 <> "//")
Error=1
End Case
*********************************************************
*If input is 10 character and the format is YYYY-MM-DD *
*********************************************************
Case (Len(Input)=10 AND (Sformat = "YYYY-MM-DD"))
DashCharac1= Iconv(Input,"MC/N")
Begin Case
Case (DashCharac1="--")
DashDate1=LEFT(Input,4): RIGHT(LEFT(Input,7),2): RIGHT(Input,2)
Convert=Iconv(DashDate1,"D-YMD[4,2,2]")
Error =0
Case (DashCharac1 <> "--")
Error=1
End Case
*********************************************************
*If input is 8 character and the format is MMDDYYYY *
*********************************************************
Case (Len(Input)=8 AND (Sformat = "MMDDYYYY"))
NormDate2=Input
Convert=Iconv(NormDate2,"D-MDY[2,2,4]")
Error =0
*********************************************************
*If input is 10 character and the format is MM/DD/YYYY *
*********************************************************
Case (Len(Input)=10 AND (Sformat = "MM/DD/YYYY"))
SlashCharac2= Iconv(Input,"MC/N")
Begin Case
Case (SlashCharac2="//")
SlashDate2=LEFT(Input,2): RIGHT(LEFT(Input,5),2): RIGHT(Input,4)
Convert=Iconv(SlashDate2,"D-MDY[2,2,4]")
Error =0
Case (SlashCharac2 <> "//")
Error=1
End Case
*********************************************************
*If input is 10 character and the format is MM-DD-YYYY *
*********************************************************
Case (Len(Input)=10 AND (Sformat = "MM-DD-YYYY"))
DashCharac2= Iconv(Input,"MC/N")
Begin Case
Case (DashCharac2="--")
DashDate2=LEFT(Input,2): RIGHT(LEFT(Input,5),2): RIGHT(Input,4)
Convert=Iconv(DashDate2,"D-MDY[2,2,4]")
Error =0
Case (DashCharac2 <> "--")
Error=1
End Case
*********************************************************
*If input is 8 character and the format is DDMMYYYY *
*********************************************************
Case (Len(Input)=8 AND (Sformat = "DDMMYYYY"))
NormDate3=Input
Convert=Iconv(NormDate3,"D-DMY[2,2,4]")
Error =0
*********************************************************
*If input is 10 character and the format is DD/MM/YYYY *
*********************************************************
Case (Len(Input)=10 AND (Sformat = "DD/MM/YYYY"))
SlashCharac3= Iconv(Input,"MC/N")
Begin Case
Case (SlashCharac3="//")
SlashDate3=LEFT(Input,2): RIGHT(LEFT(Input,5),2): RIGHT(Input,4)
Convert=Iconv(SlashDate3,"D-DMY[2,2,4]")
Error =0
Case (SlashCharac3 <> "//")
Error=1
End Case
*********************************************************
*If input is 10 character and the format is DD-MM-YYYY *
*********************************************************
Case (Len(Input)=10 AND (Sformat = "DD-MM-YYYY"))
DashCharac3= Iconv(Input,"MC/N")
Begin Case
Case (DashCharac3="--")
DashDate3=LEFT(Input,2): RIGHT(LEFT(Input,5),2): RIGHT(Input,4)
Convert=Iconv(DashDate3,"D-DMY[2,2,4]")
Error =0
Case (DashCharac3 <> "--")
Error=1
End Case
End Case
*************************************************************************
* Get the Conversion Status from the internal conversion *
*************************************************************************
ConvStatus= Status()
*************************************************************************
* Output: 1 if there is error or warning in conversion *
* 0 if the conversion is successful *
*************************************************************************
Begin Case
Case (Len(Input)=8 OR Len(Input)=10 OR Len(Input)=6) AND (Error=0)
Begin Case
Case (ConvStatus=1 or ConvStatus=2 or ConvStatus=3)
Output=1
Case ConvStatus=0 AND ( (Sformat = "YYYYMMDD" and Len(Input)=8) OR (Sformat = "YYYY-MM-DD" and Len(Input)=10 ) OR ( Sformat = "YYYY/MM/DD" and Len(Input)=10))
Output=0
Case ConvStatus=0 AND ( (Sformat = "MMDDYYYY" and Len(Input)=8) OR (Sformat = "MM-DD-YYYY" and Len(Input)=10 ) OR ( Sformat = "MM/DD/YYYY" and Len(Input)=10))
Output=0
Case ConvStatus=0 AND ( (Sformat = "DDMMYYYY" and Len(Input)=8) OR (Sformat = "DD-MM-YYYY" and Len(Input)=10 ) OR ( Sformat = "DD/MM/YYYY" and Len(Input)=10))
Output=0
End Case
Case (Len(Input) <> 8 AND Len(Input)<> 10 AND Len(Input) <> 6)
Output=1
Case (Error =1)
Output=1
End Case
*************************************************************************
* Output: 1 if there is error or warning in conversion *
* Db2 date format if the conversion is successful *
*************************************************************************
Begin Case
Case (Output=0)
Result=Oconv(Convert,"D/MDY[2,2,4]")
Case (Output=1)
Result=1
End Case
Ans= Result
Avishek Mukherjee
Data Integration Architect
Chicago, IL, USA.
Data Integration Architect
Chicago, IL, USA.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Too much detail. All you need is something like the following.
Code: Select all
FUNCTION IsValidDate(TheDate, Format)
* Format is YMD, MDY or DMY
Test = Iconv((TheDate), "D" : (Format))
Ans = Not(Status())
RETURN(Ans)
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.