How to Filter certain Date values
Moderators: chulett, rschirm, roy
How to Filter certain Date values
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
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
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![Question :?:](./images/smilies/icon_question.gif)
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
![Question :?:](./images/smilies/icon_question.gif)
If women didn't exist, all the money in the world would have no meaning.
-- Aristotle Onassis
-- Aristotle Onassis
If you dont mind using the transformer you could do the following in the constraint
This will give you all records whose date length is less that 10.
Code: Select all
Len(in.Date) < 10
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
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 :
then you can simply check the dates with constraint and sv :
Code: Select all
sv=len(read.date1)
Constraint : sv<>10
If women didn't exist, all the money in the world would have no meaning.
-- Aristotle Onassis
-- Aristotle Onassis
-
- Participant
- Posts: 48
- Joined: Wed Jun 01, 2005 7:10 am
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
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
use stagevariables
And give this column "DATE" SQL TYPE
Code: Select all
X = Iconv(read.date1, "D4")
Y = Oconv(X,"D-YMD[4,2,2]")
If women didn't exist, all the money in the world would have no meaning.
-- Aristotle Onassis
-- Aristotle Onassis
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
Try this:
In the Constraint use the suggestion provided by DSguru.
then convert the valid dates from varchar to date using string to date function.
ganesh, iconv and oconv are basic functions.
In the Constraint use the suggestion provided by DSguru.
Code: Select all
len(in.date) = 10 ----- for output
Reject ----- for reject
Code: Select all
StringToDate(in.date, "%mm-%dd-%yyyy")
I haven't failed, I've found 10,000 ways that don't work.
Thomas Alva Edison(1847-1931)
Thomas Alva Edison(1847-1931)
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
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
somaraju
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA