Need help on Datatype Conversion

roopkumar · Post by **roopkumar** » Tue Nov 29, 2011 4:23 am

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.

chulett · Post by **chulett** » Tue Nov 29, 2011 7:25 am

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.

roopkumar · Post by **roopkumar** » Tue Nov 29, 2011 11:49 pm

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.

chulett · Post by **chulett** » Wed Nov 30, 2011 12:57 am

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.

kamtammystuff · Post by **kamtammystuff** » Wed Nov 30, 2011 10:38 pm

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.

qt_ky · Post by **qt_ky** » Wed Nov 30, 2011 11:38 pm

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".

kamtammystuff · Post by **kamtammystuff** » Thu Dec 01, 2011 12:44 am

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.

jwiles · Post by **jwiles** » Thu Dec 01, 2011 10:46 am

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,