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.
Need help on Datatype Conversion
Moderators: chulett, rschirm, roy
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 26
- Joined: Fri Feb 19, 2010 2:08 am
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.
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".
Choose a job you love, and you will never have to work a day in your life. - Confucius
-
- Participant
- Posts: 26
- Joined: Fri Feb 19, 2010 2:08 am