developers

    Forum Stats

  • 3,873,649 Users
  • 2,266,621 Discussions
  • 7,911,596 Comments

Discussions

Xquery dateTime format conversion

bhaskar
bhaskar Member Posts: 51
edited Dec 2, 2013 9:41AM in XQuery

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

bhaskar

Best Answer

  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    Answer ✓

    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")
    
«1

Answers

  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy

    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) ?

  • bhaskar
    bhaskar Member Posts: 51

    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

  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    edited Nov 26, 2013 7:16AM

    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")
    
    
    odie_63
  • bhaskar
    bhaskar Member Posts: 51

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

    Cheers,

    Bhaskar

  • bhaskar
    bhaskar Member Posts: 51

    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

  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    Answer ✓

    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")
    
  • tsuji
    tsuji Member Posts: 179 Bronze Badge

    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.)

    tsuji
  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    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.

    bhaskar
  • bhaskar
    bhaskar Member Posts: 51
    edited Dec 2, 2013 6:00AM

    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

  • bhaskar
    bhaskar Member Posts: 51

    Thanks Odie and Tsuji.

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

    Cheers,

    Bhaskar

This discussion has been closed.
developers