populating valid date

[ravindrag]

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

[ArndW]

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.

[avi21st]

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.

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 need

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

[ray.wurlod]

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)