How to Filter certain Date values

[gvstrao]

Hi,

I have a requirement like this . I have a source file with employer hire date and it has varchar (10) length for some records I am getting date as 9/1/2003 (d/m/yyyy) and for some I am getting date as mm/dd/yyyy (12/03/2006) so I need to filter out the records that are in d/m/yyyy format . And I need to convert varchar from input to date in the output with mm/dd/yyyy format.

Please can any one tell me how to do that


Thanks,
Rao

[ganesh123]

If its varchar2 then how can you identify from source that date is in which format ??

I mean mm/dd/yyyy and dd/mm/yyyy ..!!

suppose dd/mm/yyyy is something like 03/03/2007, but you dont know the format then how can you judge that its in mm/dd/yyyy format or dd/mm/yyyy

[DSguru2B]

If you dont mind using the transformer you could do the following in the constraint

Code: Select all

Len(in.Date) < 10

This will give you all records whose date length is less that 10.

[ganesh123]

Now as you have mentioned length is 10 and if your format is d/m/yyyy

then you can simply check the dates with constraint and sv :

Code: Select all

sv=len(read.date1)

Constraint : sv<>10

[mansoor_nb]

Check for the length of the input column, if the length is less than 10 then convert it into mm/dd/yyyy else pass the input column to the target.

[gvstrao]

Hi ,

Thanks for the reply. I tried to convert the date from varchar to date in transformer using String to Date function . String to Date ( Employee_Hire Date , "%yyyy-%mm-%dd") but Iam getting warnings and I got warning as


Transformer_13,0: Conversion error calling conversion routine date_from_string data may have been lost

Can some one tell me how to convert varchar to date fromat yyyy-mm-dd format

Thanks,
Rao

[ganesh123]

use stagevariables

Code: Select all

X = Iconv(read.date1, "D4")
Y = Oconv(X,"D-YMD[4,2,2]")

And give this column "DATE" SQL TYPE

[us1aslam1us]

Try this:

In the Constraint use the suggestion provided by DSguru.

Code: Select all

 len(in.date) = 10  ----- for output
  Reject            ----- for reject

then convert the valid dates from varchar to date using string to date function.

Code: Select all

 StringToDate(in.date, "%mm-%dd-%yyyy")

ganesh, iconv and oconv are basic functions.

[DSguru2B]

ICONV/OCONV wont work in parallel jobs.
First you need to seperate records which have date length less than 10. The ones that pass, apply StringToDate() function to them.

[ganesh123]

Yup my bad. Sorry, I thought I am in Server section. A smart excuse huh

I was excited on Indian performance today in Cricket so didnt see proper ly. :D A great victory in World cup history today :D

[somu_june]

Hi ,

while I am using this function StringToDate(in.date, "%yyyy-%mm-%dd") in the target flat file I mentioned the datatype as Date and length as 10 and when I ran the job it is showing that records are processed and when I open the file with view tab it is giving the following warning


<Sequential_File_26,0> Field "STL" has import error and no default value; data: {* * * * * * * * * *}, at offset: 1

Sequential_File_26,0> Import complete; 0 records imported successfully, 3 rejected.
**VIEWDATA ROW LIMITER HIT**



Thanks,
Rao

[DSguru2B]

Please do not hijack threads. Start your own and give explanation of what the incoming date looks like.

[somu_june]

Hi,


Iam getting the same varchar as the input.





Thanks,
Raju

[us1aslam1us]

It seems you are having either null values or blanks in that column. Handle Nulls and blank data before doing the conversion.