string to date

[suryadev]

for the null values in the field I gave date like 9999/12/31

now when I see in the table externally it seems to be working well in both the fields.

but when I view the records in the DB2 stage (target)

I see all the records appear like... 233673524 (9999/12/31)

any environment variable required?

[anbu]

Dates are stored internally in DB2 as the numbers you see in DataStage. If you have used format function in Target stage sql, then you will see it in date format

[suryadev]

my target stage is DB2 UDB....where can I find the format function in DB2 stage?

please..let me know....

[chulett]

but when I view the records in the DB2 stage (target)

I see all the records appear like... 233673524 (9999/12/31)

And... ? There's nothing wrong here.

[suryadev]

this is the view when I see in view of DB2 stage for one field but when I view the same field in the table I can view it in proper way like..9999/12/31.

but there is an other field which is null(date null)

when I view that field in the table I cannot view the proper date also...all the null fields look like... 4713/1/1
why does the null values look so?

[chulett]

this is the view when I see in view of DB2 stage for one field but when I view the same field in the table I can view it in proper way like..9999/12/31.

There is no 'proper' way. Both are correct, it's just the external presentation that is different and as noted the stage is throwing in the stored/internal numeric representation of the date as a bonus, no charge.

but there is an other field which is null(date null)
when I view that field in the table I cannot view the proper date also...all the null fields look like... 4713/1/1
why does the null values look so?

Because you have mis-loaded them, it seems. Show us exactly what you are doing for these null dates, cut and paste your derivation into your post.

[suryadev]

Exact presentation of the null dates column is

1)renewal date is the column which I am reading from a file and the datatype is varchar
2)the renewal date was null for all the records and looked like " " in the file.
3)removed the quotes by using field(dslink102.renewaldate,'"',2) which has the same datatype until this point in transformer
4) In the next transformer the input is renewaldate,varchar,nullable-yes
and the output is renewaldate,data,nullable-yes as used the stringtodate function and changed the datatype....after this

the link is to the target i.e the table.

[chulett]

As requested, post the syntax for step #4. That's where things are going wrong.

[suryadev]

transformerinput-----renewaldate,varchar,yes
columnderivation-----

Code: Select all

StringToDate(DSlink102.renewaldate,"%yyyy/%mm/%dd")

tranformeroutput-------renewaldate,Date,yes

thats the syntax chulett,

thanks

[chulett]

We're going around in circles here.

This is meant to be the "null" date for your target column that does not accept nulls, yes? If so, where is the implementation of the "default" date that was mentioned earlier? The earlier post where you said you were setting them to "12/31/9999" is what I am referring to. And not seeing.

If this target accepts nulls, then just send the null, don't use the StringToDate() conversion function at all in that case.

[suryadev]

doesnot accept nulls is for other field(acceptdate) which is also date but NOT NULL and the default date is given to that field.

this field(renewaldate) can accept nulls as it is date NULL
As you said If I do not use stringToDate() conversion function at all than If the dates come in this field renewaldate.

Do they load in a proper way with out the conversion?
and do I need to change the datatype to date before the target stage?

[chulett]

No, just simply do an if-then-else construct: if the incoming field is null then send through that incoming value unmolested, otherwise use the StringToDate() function.

[suryadev]

So..is it something like below manner

tranformer input-----renewaldate,varchar,yes

column derivation--------

Code: Select all

If IsNull(DSLink102.renewaldate) then '9999/12/31'

else DSLink102.renewaldate

transformer output--renewaldate,date,yes

[chulett]

No... that would be bass-ackwards and there's no need for the 'in-band' null value here, since nulls are allowed.

Code: Select all

If IsNull(DSLink102.renewaldate) then DSLink102.renewaldate 
else StringToDate(DSlink102.renewaldate,"%yyyy/%mm/%dd")

[suryadev]

yes used the same code and changed the datatype to date in the output of the transformer.

now the field look like the null fields have nulls in them and the rest of them have 1/1/4713.

is it because the datatype has changed or is this the default value given in the string to date() convertion