string to date

[suryadev]

tried to change the datatype varchar() to date for two fields and one is null and the other one not null.

used the function string to date(dslink102.date1,"%yyyy/%mm/%dd")
but the data is not loaded in the table.

also tried to use sequential file as target but no use.


Do I need to change the function?

Thanks

[suryadev]

I see number of warnings of the below kind when I use target as a table.


DB2_UDB_API_115,0: Invalid date value in getInputFieldAsDate() for field '3' of input dataset '0'.

[anbu]

Can you post sample input date?

[suryadev]

The sample date is "2009/04/29" which is read as varchar

so I removed the double quotes by using field(dslink102.date1,'"',2)

and the column is not null so removed nulls Is IsNull(dslink102.date1) then ' '
else dslink102.date1 with the same data type varchar.

at last to change the datatype from varchar to date.
used the function string to date.

[anbu]

Code: Select all

IsNull(dslink102.date1) then ' ' 

Single space is not valid date. Assign default date

[suryadev]

gave two spaces but same warning.

Is there any steps to be followed or nothing like that,I mean to say

first get rid of quotes and then IsNull and then datatype...

[suryadev]

actually the field should be changed to not null and also the format from string to date....

the table is not accepting the values after the format change.
it says the date format is wrong

used the string to date function.

please help with any changes required in the function or the format

[chulett]

Post the actual, unedited error plus the exact syntax you used in your job.

[suryadev]

I removed the null values in one of the fields by using
If IsNull() then ' ' else

after that

when I tried to change the format from varchar date I got the below error.
and the function I used for that is stringtodate
this is the exact error.

DB2_UDB_API_115,0: Invalid date value in getInputFieldAsDate() for field '3' of input dataset '0'


the table has 5 fields and in those 2 fields has DATE NULL and DATE NOT NULL

so to make the changes and to load in the tables I used transformer with the
functions mentioned.

[chulett]

A null date is valid as long as the target accepts nulls. Under no circumstances is any number of spaces a valid date.

[suryadev]

AS the target is not null I changed the incoming rows from null to ' ' .
will that satisfy the condition?

If so do I need to change the function used to convert the datatype from string to date

[chulett]

AS the target is not null I changed the incoming rows from null to ' ' .
will that satisfy the condition?

Has it satisfied it yet? As noted, spaces are not a valid date. You need to use an 'in-band' null value, an actual date that represents null based on your business rules, say like "12/31/9999".

[suryadev]

yes,changed the null values to '9999/12/31' and after this I need to convert the data type from string to date.

Will this format be according to the DB format or the regular format.

I mean mm/dd/yyyy or yyyy/mm/dd.

[chulett]

No, you've said you need to convert to a TIMESTAMP not a DATE. Regardless, any external format will work as long as you match that in the function call with the proper mask.

[suryadev]

sorry If I said its a time stamp, but its not a time stamp its a date.

used the function works well for one column which is not null but for another column which is null the format is changed to date but in the table the records appear like
1/1/4713 for that particular field,which is also date.