How to convert hex 'YMD' date to a readable date?

Question

I've been given a file with a bunch of dates inside. They're in Hex format and I'm told it's YMD.

Example is: 4A273F

..any idea how I'd go about converting it? Idealling using SQL/PLSQL but I'm open to other ideas.

thanks!

Answer 1

Oracle and java play fairly well with each other, and this is may be a new idea if you're feeling adventerous. Java does this pretty easily, but i'm away from an oracle box and unable to test. This really hurts i'm not in front of an oracle machine at the moment. To make sure I remembered correctly, I used steps from here http://www.dba-oracle.com/tips_oracle_sqlj_loadjava.htm

and then I googled this, which is almost word for word what we'll do below: http://docstore.mik.ua/orelly/oracle/guide8i/ch09_03.htm

/****
*java and oracle playing together.
 */
import java.util.Date;
public class hex2Date{
   //this is if you want to call the java function from a command line outside of oracle
     public static void main (String args[]) {
         System.out.println ( convert (args[0]) );    
    }

     public static Date convert(String inHexString){
       try{
        Date dateResult = new Date(Long.parseLong(inHexString,16));
       }catch (NumberFormatException e){
       // do something with the exception if you want, recommend at least throwing it.
    //throw(e,'some extra message');

       }

        return dateResult;
   }
}

save that file as Hex2Date.java in 'yourJava' directory, and from a cmd line in that directory type:

javac Hex2Date.java

now type:

java Hex2Date.java 0x4A273F

if you get the right dates, and the results, let's tell oracle about our little function. you'll need some user rights first: GRANT JAVAUSERPRIV TO your user here; type this: loadjava -user scott/tiger -resolve Hex2Date.class

now wrap this in pl/sql, so you can call it in pl/sql.:

/* java function plsql wrapper: hex2Date.sf */ 
CREATE OR REPLACE FUNCTION hex2Date (    inHexString IN VARCHAR2)     RETURN DATE AS LANGUAGE JAVA    NAME 'Hex2Date.convert(java.lang.String)              return Date'; /

now, run it from a sql prompt: SQL> exec DBMS_OUTPUT.PUT_LINE ( hex2Date('0x4A273F'))