Thanks guys for your replies.

unfortunately that was an issue from the vendor. They initially said that it is a date field and that was the reason I was trying to convert it to date format.

But later after several discussions we had to do the following to resolve it.

Select LTRIM(Convert(varchar(112), 0x20202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020323939343730, 0) )

The result set for the above will be - 299470

And then the vendor said for their software the dates will be calculated by subtracting the above result with 999999 which will result in YYMMDD date format

i.e. 999999 - 299470 = 700529 (so this result is in the for of YYDDMM format)

Thanks again guys and sorry for confusion.

Thanks,

grkanth81