[resolved] Check if a string is a valid date pattern ?

[Anonymous]

Hi there,
I have a CSV File as a source, one of the field is supposed to be a date
But sometimes, some rows deliver this field with an invalid string for a date (for example : "378-35-23", or "NaN-NaN-NaN", etc ...)
I would like to test the content of this field to:
1* Load the correct rows in the target database
2* Reject the bad rows in an output file or Replace the bad data by a default one (NULL or 01/01/1900 for example)
Should I use tFilterRow ?
What is the method to valid the string pattern ?
How should I declare the data type for this field in my source input file ?

thx
Phil

[Anonymous]

Hello

Should I use tFilterRow ?
What is the method to valid the string pattern ?
How should I declare the data type for this field in my source input file ?

Yes, you can use the tFilterRow to filter the invalid date and output them in a file, declare the data type for this field as string. All the valid data in this field should have a unite format, for exmaple the length of them are the same. After filter all the valid data, you can convert a String to a Date. for example,

TalendDate.parseDate("MM/dd/yyyy",06/26/2008)

Best regards
shong

[Anonymous]

ok thx shong

is there any method like isValidDate(<<myString as string>>, <<myDateFormat as string>>) return 0 or 1 for OK or Not OK ??

[Anonymous]

is there any method like isValidDate(<<myString as string>>, <<myDateFormat as string>>) return 0 or 1 for OK or Not OK ??
Did you get the answer yet / please share if you do

[Anonymous]

I don't know about TalendDate.parseDate, but SimpleDateFormat.parse throws exception u could catch and return false otherwhise true.

[c0utta]

sahanakrish, double posting and reviving old threads is frowned upon in most forums.

[Anonymous]

Hello, relatively new to Talend, trying to help out a friend.
Trying to convert SQL Server date time value into 8 digit int for fact table (DateKey) column that is an FK to a Date Dim. Not having much luck with Expression Builder.
So from:
"2008-09-29 01:56:26.393" to "20080929"
Convert(VARCHAR(10), DomAtyVisitDateEnt, 112) AS - gets me the desired results in T-SQL.
And if possible the same for the FK (TimeKey) Time Dim
"2008-09-29 01:56:26.393" to "015626"
Any ideas or references would be great. The job is in "Java" (not Perl) if that helps.
Thanks, Mircea

[Anonymous · Accepted solution]

Hello Mircea

Convert(VARCHAR(10), DomAtyVisitDateEnt, 112) AS - gets me the desired results in T-SQL.

In Talend, you don't need use the convert function on sql, just define the date pattern on the schema of input/output component.
on the schema of input component, set the date pattern as:
"yyyy-MM-dd HH:mm:ss.sss"
on the schema of output component, set the date pattern as:
"yyyyMMdd"
Best regards
shong

[Anonymous]

Shong;
Thanks for your response. I am still just a little bit confused.
When you say input/output component (singular) are you talking about the tMap component? Or did you mean the input (data source) and the output (target) components? If the latter, am I editing the schema on the component itself?
Thanks, Mircea

[Anonymous]

Hello

If the latter, am I editing the schema on the component itself?

I mean the latter point, for example:
tMSSQLInput---tLogRow
on the schema of tMSSQLInput, set the date pattern as:
"yyyy-MM-dd HH:mm:ss.sss"
on the schema of tLogRow, set the date pattern as:
"yyyyMMdd"
Best regards
shong