Creating UDF for converting HEX to date...HELP

M_RAS · #1 (**permalink**) 09-28-05, 09:58

Hi All,
I am working with db2 v8.2.2 and AIX 5.2,
I need to create UDF with Db2 Development Centre to convert HEX (.e.g x'00256859000000000804011B') to date like 2000-01-01. Actually I am new in creating UDF, I appriciate any help so that I can write a program (SQL) for this.
Thank you in advance for your help

n_i · #2 (**permalink**) 09-28-05, 10:17

Code:

create function schemaname.fun_udf(p_hex char(13) for bit data)
  returns date
  LANGUAGE SQL
  NO EXTERNAL ACTION
  DETERMINISTIC
  RETURN
    values (
      case p_hex 
        when x'00256859000000000804011B' then date('2000-01-01') 
        else date ('9999-01-01') 
      end 
    )

You may need to adjust date calculation algorithm.

M_RAS · #3 (**permalink**) 09-28-05, 10:28

Quote:

Originally Posted by n_i

Code:

create function schemaname.fun_udf(p_hex char(13) for bit data)
  returns date
  LANGUAGE SQL
  NO EXTERNAL ACTION
  DETERMINISTIC
  RETURN
    values (
      case p_hex 
        when x'00256859000000000804011B' then date('2000-01-01') 
        else date ('9999-01-01') 
      end 
    )

You may need to adjust date calculation algorithm.

Thank you so much n_i for your quick response, The values that I pointed to them( x'00256859000000000804011B') were just sample how can I write that in general so that convert HEX to date, I will appreciate if you had time help me for writing this UDF.

n_i · #4 (**permalink**) 09-28-05, 11:26

How about this:

Code:

values (
  date(int(
    ascii(substr(p_hex,11,2))+
    ascii(substr(p_hex,9,1))*256 + 
    ascii(substr(p_hex,7,2))*256*256 +
    ascii(substr(p_hex,5,2))*256*256*256 
  ))
)

M_RAS · #5 (**permalink**) 09-28-05, 12:10

Quote:

Originally Posted by n_i

How about this:

Code:

values (
  date(int(
    ascii(substr(p_hex,11,2))+
    ascii(substr(p_hex,9,1))*256 + 
    ascii(substr(p_hex,7,2))*256*256 +
    ascii(substr(p_hex,5,2))*256*256*256 
  ))
)

Thank you again n_i
I ran the above sql in CLI with p_hex = x'00256859000000000804011B' the return value was 2049, does it make sence ? I expected it should be 2000-01-01 (because in our application the return value is 2000-01-01 when we are using C++ UDF (old version), I changed (substr(p_hex,9,1)) to (substr(p_hex,9,2)) as well.

M_RAS · #6 (**permalink**) 09-28-05, 12:29

Quote:

Originally Posted by M_RAS

Thank you again n_i
I ran the above sql in CLI with p_hex = x'00256859000000000804011B' the return value was 2049, does it make sence ? I expected it should be 2000-01-01 (because in our application the return value is 2000-01-01 when we are using C++ UDF (old version), I changed (substr(p_hex,9,1)) to (substr(p_hex,9,2)) as well.

\

Hi n_i

I created UDF according your suggestion and it seems we are closing to target the raw value is ;

x'00256C26000000000804011B'
x'00256C26000000000804011B'

the correct value should be like;
2002-08-31
2002-08-31

But the our value after running the above UDF is

08/11/0006
08/11/0006

n_i · #7 (**permalink**) 09-28-05, 12:32

Quote:

Originally Posted by M_RAS

the return value was 2049, does it make sence ?

Not really. However, since your question didn't make sense either I thought the answer would be ok.

M_RAS · #8 (**permalink**) 09-28-05, 12:40

Quote:

Originally Posted by n_i

Not really. However, since your question didn't make sense either I thought the answer would be ok.

Hi n_i

I was wrong when I ran JUST value command in CLI.

I created UDF according your suggestion and it seems we are closing to target the raw value is ;

x'00256C26000000000804011B'
x'00256C26000000000804011B'

the correct value should be like;
2002-08-31
2002-08-31

But the our value after running the above UDF is

08/11/0006
08/11/0006

n_i · #9 (**permalink**) 09-28-05, 13:54

Oh, I see it now: you're trying to figure out the way the date is encoded in the "hex string" by guessing? Sorry, I'm afraid I can't help you there; my crystal ball is in for repairs.

May be if you look at the "C++ UDF (old version)" source code it'll give you an insight?

M_RAS · #10 (**permalink**) 09-30-05, 12:45

Quote:

Originally Posted by n_i

Oh, I see it now: you're trying to figure out the way the date is encoded in the "hex string" by guessing? Sorry, I'm afraid I can't help you there; my crystal ball is in for repairs.

May be if you look at the "C++ UDF (old version)" source code it'll give you an insight?

Hi n_i

I resolved the problem with the following script

CREATE FUNCTION dba.hex_to_date(p_hex varchar(12) for bit data)

RETURNS char(10)

LANGUAGE SQL

NO EXTERNAL ACTION

DETERMINISTIC

RETURN values(

char(date(ascii(substr(p_hex,1,1))*16777216+

ascii(substr(p_hex,2,1))*65536+

ascii(substr(p_hex,3,1))*256+

ascii(substr(p_hex,4,1)) - 1721425), ISO))

But unfortunatly db2 v8 does not accept SQL FUNCTION in ON cluse in join statement , any idea?

n_i · #11 (**permalink**) 09-30-05, 13:36

Would that work, I wonder:

Code:

with t (dt, other) as (
  select dba.hex_to_date(x'0000..1'), otherstuff
  from table_a
  where ...
)
select * from t join table_b on t.dt = table_b.datevalue

Internet Services

Web Development

Digital Marketing

Consumer Tech