populating valid date
Posted: Tue Oct 03, 2006 11:25 am
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
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.
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
Code: Select all
FUNCTION IsValidDate(TheDate, Format)
* Format is YMD, MDY or DMY
Test = Iconv((TheDate), "D" : (Format))
Ans = Not(Status())
RETURN(Ans)