DSXchange

hi,

My job design is

odbc(db2).............>xfm...........>seqfile

i have column name CYC_DT(date) having some of values 2011-02-23 and some of the values are null and in output i want 20110223 instead of 2011-02-23 and null values show null .

I have use convert function in tansformer but still i am getting the date 2011-02-23.so could anyone me on this issue.

Try below one.

If IsNull(CYC_DT) then SetNull() else (convert("-","",DateToString((CYC_DT)))

Make sure your output is a string and use an appropriate mask for the DateToString() function, i.e. one without delimiters.

chulett,

you didn't mention about null handling, can the DateToString() function take null parameter values?

thanks

No, it can't work with nulls, so as Ravi posted you need to handle those first.

Your dates are of data type Date or String? If they're of type Date (which I suspect they are), they are binary values within DataStage, only translated to your default date format where necessary for display (for example View Data) or when being converted to string (for example being exported to a text file). You probably need to convert non-null dates to strings as noted by fmou. Further, the Convert() function you specified will convert the "-" to space characters; you really need "" (zero length string) as the second argument to effect your requirement.

Hi
My input Datatype for column CYC_DT is date so i have to change to string using the following code as mentioned by Ravi.
Also i have to take care of null first as code shows and then covert to output string Varchar or char

Please reply me .

As CYC_DT is a date datatype, instead of Convert("-","",DateToString(CYC_DT) use DateToString(CYC_DT,"%yyyy%mm%dd").

As noted.

Handle nulls as mentioned and you can also use...

datecolumn[1,4]:datecolum[6,2]:datecolumn[9,2] (if the date column is not null) instead of DateToString Function.

One small doubt I have is.. is this method faster or direct DateToString function faster..

Can anyone suggest about the difference in performance between the inbuilt function and the logic i mentioned.

Internally at the core level.. Both the codes work in the same way So its touch to say which one works better But I Prefer to use a pre-defined function since it is handy.

But if your requirement demands you to use substings then.. thats fine.

Simple thing you can do is to use TRIM function to remove those '-'.

Trim(CYC_DT,"-","A").

and further to if you want same with the NULL handling then,

If IsNull(CYC_DT) Then "NULL" Else Trim(CYC_DT,"-","A")

Use Stage Variable.

To answer kiran's question:

I will argue that the using the built-in DateToString() function as suggested by Craig and myself will be faster than the other options that have been mentioned. Here's why:

1) The column in question is a date datatype and is stored internally in a binary format.
2) The DateToString() function will convert the internal date format directly to a string using either the supplied format string or the installation default format string if not given a format.
3) The other suggestions, while they should work, all use functions which require string inputs. This will force the compiler to convert the date to a temporary string before applying the suggested functions, adding overhead as compared to simply allowing DateToString() to do the conversion itself. Besides, all of these options require that the default date format string be of the appropriate format to meet the final format requirement.

An easy way for you to see what is happening internally is to create a transformer which performs all of the suggested conversions, compile the job and look at the transformer source in the appropriate RT_SC# directory (*.trx files). It should be pretty obvious from the source which will be the most efficient.

*Edit* I will add this statement as a cop out : Compiler optimizations may invalidate the above statements as far as final efficiency is concerned...I did not take optimizations into account. You may want to consider which option is more readable or maintainable in your situation.

Regards,

hi all,

thanks for giving information i got the solution