DSXchange

Hi All,

I have a requirement that i have to convert from varbinary format to Date format.

The derivation for the fields is
Start date = SUBSTR(<Source field value>, 1,10) and
End date = SUBSTR(<Source field value>, 12,10) and
both should be loaded in format MM/DD/YYYY.

But the sample values for varbinary field are like below
"30362f30312f323031312c31322f33312f39393939"

I tried in some ways, but not able to fix it. How can i transform this varbinary field to Date format?

Please help me if you have any idea.

Thanks In Advance,
Rgds,
RooP KumaR.

What you posted is hexidecimal. For example, "30362f30312f32303131" is "06/01/2011" so your first task would be to that to character, it would seem.

Hi Chulett,

Thanks for the reply. Can you please let me know how you converted this hexidecimal to Date Data type. Means which Transformer function you used to covert this ?

Thanks In Advance.

Rgds,
RooP KumaR.

Sorry, I just converted it by eye... 30 is zero, 31 is one, etc. I do believe there are "conv" functions in Server for this but I'm afraid I don't know of the equivalent for a Parallel job. Hopefully someone else can help with the specifics.

I guess this is just a conventional representation of dates and by following chulett's rpresentation the given string clearly gives us 2 dates as mentioned below

06/01/2011:12/31/9999 ====> where the first date represents the start date and the second represents the End date.

So you can convert the strings in to two date fields with the convention that 30 represents '0' , 31 as '1' ..... and '2f' represents the delimeter in date and '2c' represents the delimiter between dates.

Would the RawToString function help? Example from the Parallel Job Developer's Guide (v8.5):

If the column mylink.myraw contains the value { 0x31 0x31 0x30 0x35 0x32 0x32 0x30 0x39 }, then the following function returns the string "11052209".

If the column mylink.myraw contains the value { 0x31 0x31 0x30 0x35 0x32 0x32 0x30 0x39 }, then the following function returns the string "11052209".

In that case first you need to filter '2f' and '2c' first and then insert the delimiters after converting.

Not necessarily...the example is just that, an example. It is in no way implying that RawToString() cannot convert the 2c and 2f hex values.

Regards,