Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230

    Unanswered: Creating UDF for converting HEX to date...HELP

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.

  3. #3
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230
    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.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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 
      ))
    )

  5. #5
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230
    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.

  6. #6
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230
    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

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.

  8. #8
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230
    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

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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?

  10. #10
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230
    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?

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •