This discussion is archived
11 Replies Latest reply: Dec 2, 2013 6:41 AM by tsuji RSS

Xquery dateTime format conversion

Bhaskar Newbie
Currently Being Moderated

Hi All,

 

I need an help for date and time format conversion. A working java method has to be converted to Xquery function to be integrated into fusion middleware. Not sure whether any suitable Xquery function is existing for this.

 

Scenario:

-------------

Basically, I have inputDate (instance of SimpleDateFormat in java) defined in "MM/dd/yyyy HH:mm:ss" and outputDate in "MMddyyyy" format. Any incoming String should be parsed into inputDate format and then converted to outputDate format.

 

Example : Incoming String - 12/26/2013 14:21:33  Outcoming 12262013

 

I know in java it is easy, but any suggestions in xquery, appreciated.

 

Thanks,

Bhaskar

  • 1. Re: Xquery dateTime format conversion
    odie_63 Guru
    Currently Being Moderated

    Is the input string guaranteed to be in the "MM/dd/yyyy HH:mm:ss" format, or do you also want to check for format mismatch and return an error (if any) ?

  • 2. Re: Xquery dateTime format conversion
    Bhaskar Newbie
    Currently Being Moderated

    Hi Odie,

     

    Yes, the input string will be adhering to have "MM/dd/yyyy HH:mm:ss" format. The only check is, if at all it is null then, some hard coded value to be assigned, which is again in MMddyyyy format.

     

    Thanks,

    Bhaskar

  • 3. Re: Xquery dateTime format conversion
    odie_63 Guru
    Currently Being Moderated

    Ok, then how about some simple string manipulations?

     

    declare function local:formatDate ($dt as xs:string*) as xs:string {

      if (not($dt)) then "01011970"

      else concat(

             substring($dt,1,2)

           , substring($dt,4,2)

           , substring($dt,7,4)

           )

    };

    local:formatDate("12/26/2013 14:21:33")

  • 4. Re: Xquery dateTime format conversion
    Bhaskar Newbie
    Currently Being Moderated

    Thanks Odie, I think it should work. Let me check and comeback.

     

    Cheers,

    Bhaskar

  • 5. Re: Xquery dateTime format conversion
    Bhaskar Newbie
    Currently Being Moderated

    HI Odie/Members,

     

    There is a small change that I over looked. Sorry about that.

     

    As I mentioned that the inputDate will always be adhere to the format "MM/dd/yyyy HH:mm:ss" i.e "12/26/2013 14:21:33", but in some cases (after looking to the test data)

    it is also in format "M/d/yyyy HH:mm:ss" i.e. "1/2/2013 14:21:33". That is, if date or month is single digit it does not forcible pad it with 0. Ideally it should be "01/02/2013 14:21:33" but it is not.

     

    For this scenario, the above transformation does not work.

     

    I am trying to break the existing string into tokenized format like

    1. "1/2/2013 14:21:33" -> "1/2/2013" and "14:21:33"

    2. Then again breaking "1/2/2013" to tokens "1" "2" "2013"

    3. leftpad with 0 if they are single digit and then apply your transformation. But some where in step 2 it is not working as expected.

     

    Any suggestions?

     

    Once again sorry for late reply.

     

    Cheers

    Bhaskar

  • 6. Re: Xquery dateTime format conversion
    odie_63 Guru
    Currently Being Moderated

    Here's one way :

    declare function local:formatDate ($dt as xs:string*) as xs:string {

      if (not($dt)) then "01011970"

      else

        string-join(

          for $i in tokenize(substring-before($dt, " "), "/")

          return if (string-length($i) = 1) then concat("0", $i) else $i

        , ""

        )

       

    };

    local:formatDate("1/2/2013 14:21:33")

  • 7. Re: Xquery dateTime format conversion
    tsuji Journeyer
    Currently Being Moderated

    Under normal circumstances, when I use regex-related built-in functions, in particular the tokenize(), I would suggest specifying the implementation built-in namespace of prefix ora, namely, ora:tokenize(). (It is not supported in the fn namespace and local namespace, and also it requires at least 11gR2 - not even 11gR1.)

  • 8. Re: Xquery dateTime format conversion
    odie_63 Guru
    Currently Being Moderated

    tsuji wrote:

     

    Under normal circumstances, when I use regex-related built-in functions, in particular the tokenize(), I would suggest specifying the implementation built-in namespace of prefix ora, namely, ora:tokenize(). (It is not supported in the fn namespace and local namespace, and also it requires at least 11gR2 - not even 11gR1.)

    Of course, that's assuming OP is using the Oracle DB implementation, which I don't think he is.

    See the first post, he mentioned Fusion Middleware so most likely OSB, in which fn:tokenize is available.

  • 9. Re: Xquery dateTime format conversion
    Bhaskar Newbie
    Currently Being Moderated

    Thanks Odie for this wonderful xquery. Meanwhile your reply appeared, I was managed write a trivial xquery which some how like this:

     

    declare function local:formatDate ($dateString as xs:string*) as xs:string {

      if (not($dateString)) then "04011"

      else

     

          let $day := fn-bea:pad-left(tokenize(tokenize($dateString, "\s")[position()=1],"/")[position()=1],2,"0")

          let $month := fn-bea:pad-left(tokenize(tokenize($dateString, "\s")[position()=1],"/")[position()=2],2,"0")

          let $year := fn:substring(tokenize(tokenize($dateString, "\s")[position()=1],"/")[position()=3],1,1)

          return concat ($day, $month, $year)

     

    };

     

    local:formatDate("1/2/2013 14:21:33")

     


    But your one looks more smart, short and performance wise better. Thanks once again for this one. I have marked the post as correct answer.


    Cheers,

    Bhaskar

  • 10. Re: Xquery dateTime format conversion
    Bhaskar Newbie
    Currently Being Moderated

    Thanks Odie and Tsuji.

     

    Yes, I am using OSB only where xquery is the mostly used transformation tool.

     

    Cheers,

    Bhaskar

  • 11. Re: Xquery dateTime format conversion
    tsuji Journeyer
    Currently Being Moderated

    Thanks, odie, for the further precision. I appreciate it.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points