3 Replies Latest reply: May 15, 2014 10:10 AM by Data Kruncher RSS

    CTOD - Buggy function or just me?

    mdyoung _

      Product: Monarch Pro v10.5[/I]

       

       

      I've been playing around with the Ctod function and can't understand why it works with some extraction formats and not with others. I've experimented with this using a calculated field with data type date/time, and tried it with all the possible format options, but mostly the General[/I] and Short Date/Time[/I] formats.

       

      The following is a snippet out of the help documentation pertaining to the valid formats for the [B]date format[/B][/I] parameter. However, the only one that doesn't seem to work is the "y/m/d" format.

      The optional date format[/I] parameter tells Monarch how to interpret the date. Valid options for this parameter are "m/d/y", "d/m/y" and "y/m/d" (the quotation marks are required).

      /QUOTE

       

      These two work fine:

      Ctod("2/1/2000 02:00", "m/d/y")[/SIZE]

      [SIZE=2]Ctod("2/1/2000 02:00", "d/m/y")[/SIZE]

      /SIZE[/CODE]

      This does not work, but, according to documentation, should work:

      Ctod("2/1/2000 02:00", "y/m/d")[/SIZE]

      /CODE

       

      Another problem is the [B]optional date/time extraction pattern[/B][/I] parameter. Here's the snippet from the documentation for valid patterns, but only the "DT" pattern works.

      The optional date/time extraction pattern[/I] parameter specifies the "date/time extraction pattern". Possible extraction patterns are as follows:D

      Pattern

      Meaning

      "D"

      Extract a date.

      "DT"

      Extract a date followed by a time.

      "T"

      Extract a time.

      "TD"

      Extract a time followed by a date.

      /QUOTE

       

      Since the "m/d/y" date format works, I'll use that to show the issue faced with the extraction patterns.

       

      This works fine.

      Ctod("2/1/2000 02:00", "m/d/y", "DT")[/SIZE]

      /CODE

       

      These do not work, but, again should according to Help documentation.

      Ctod("2/1/2000 02:00", "m/d/y", "D")[/SIZE]

      Ctod("2/1/2000 02:00", "m/d/y", "T")[/SIZE]

      Ctod("2/1/2000 02:00", "m/d/y", "TD")[/SIZE]

      /CODE

       

       

      Is this a known bug or am I mis-understanding the documentation and doing something wrong?

       

      Thanks,

       

      Micheal

        • CTOD - Buggy function or just me?
          Data Kruncher

          Hi Micheal,

           

          Notice that with these:

          [SIZE=2]Ctod("2/1/2000 02:00", "m/d/y")[/SIZE][SIZE=2][SIZE=2]

          Ctod("2/1/2000 02:00", "d/m/y")[/SIZE][/SIZE][/CODE]They return February 1st, 2000 and January 2nd, 2000. The formatting string isn't meant for you to specify the output format, it's for you to tell Monarch the format of the input.

           

          That's why this expression:

          [SIZE=2]Ctod("2/1/2000 02:00", "y/m/d")[/SIZE][/CODE]cannot be resolved by Monarch and it returns a null value, as it sees Month: January, Day: 2000, Year 2.

           

          The optional date/time parameter functions similarly, in that you need to tell Monarch how to treat your string. So for the example:

          [SIZE=2]Ctod("2/1/2000 02:00", "m/d/y", "DT")[/SIZE][/CODE]it works properly (gives a result), because you've supplied what you "promised" to supply:  a date followed by a time.

           

          The others fail because the supplied string isn't in the expected format. That is, only a date, only a time, and a time followed by a date, respectively.

           

          A little clearer now, hopefully?