Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-36216

TO_CHAR FM format not recognized in SQL_MODE=Oracle

Details

    Description

      The format description FM which toggles Fill Mode for TO_CHAR when used to dates is not recognized. For example:

      SELECT CONCAT('/', TO_CHAR('2020-01-06 10:11:12', 'DAY'), '/');

      Returns (note two spces at the end to fill up to the length of the longest day name (Wed):

      /Monday  /

      The FM flag in Oracle negates this blank filling:

      SELECT CONCAT('/', TO_CHAR('2020-01-06 10:11:12', 'FMDAY'), '/');

      Returns:

      /Monday/

      In MariaDB the result of the latter is an error:
      ERROR 3047 (HY000): Invalid argument error: date format not recognized at FMDAY in function to_char.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          karlsson Anders Karlsson created issue -
          karlsson Anders Karlsson made changes -
          Field Original Value New Value
          serg Sergei Golubchik made changes -
          Issue Type Task [ 3 ] Bug [ 1 ]
          serg Sergei Golubchik made changes -
          Affects Version/s 10.5 [ 23123 ]
          Affects Version/s 10.6 [ 24028 ]
          Affects Version/s 10.11 [ 27614 ]
          Affects Version/s 11.4 [ 29301 ]
          serg Sergei Golubchik made changes -
          Fix Version/s 10.11 [ 27614 ]
          Fix Version/s 11.4 [ 29301 ]
          Fix Version/s 11.8 [ 29921 ]
          serg Sergei Golubchik made changes -
          Description The format description FM which toggles Fill Mode for TO_CHAR when used to dates is not recognized. For example:
          SELECT CONCAT('/', TO_CHAR('2020-01-06 10:11:12', 'DAY'), '/');
          Returns (note two spces at the end to fill up to the length of the longest day name (Wed):
          /Monday /
          The FM flag in Oracle negates this blank filling:
          SELECT CONCAT('/', TO_CHAR('2020-01-06 10:11:12', 'FMDAY'), '/');
          Returns:
          /Monday/
          In MariaDB the result of the latter is an error:
          ERROR 3047 (HY000): Invalid argument error: date format not recognized at FMDAY in function to_char.           		The format description FM which toggles Fill Mode for TO_CHAR when used to dates is not recognized. For example:
          {code:sql}SELECT CONCAT('/', TO_CHAR('2020-01-06 10:11:12', 'DAY'), '/');{code}
          Returns (note two spces at the end to fill up to the length of the longest day name (Wed):
          {noformat}/Monday /{noformat}
          The FM flag in Oracle negates this blank filling:
          {code:sql}SELECT CONCAT('/', TO_CHAR('2020-01-06 10:11:12', 'FMDAY'), '/');{code}
          Returns:
          {noformat}/Monday/{noformat}
          In MariaDB the result of the latter is an error:
          ERROR 3047 (HY000): Invalid argument error: date format not recognized at FMDAY in function to_char.
          serg Sergei Golubchik made changes -
          Assignee Alexander Barkov [ bar ]
          julien.fritsch Julien Fritsch made changes -
          julien.fritsch Julien Fritsch made changes -
          Priority Major [ 3 ] Critical [ 2 ]
          julien.fritsch Julien Fritsch made changes -
          Priority Critical [ 2 ] Major [ 3 ]
          ralf.gebhardt Ralf Gebhardt made changes -
          Labels Compatibility Compatibility Oracle
          bar Alexander Barkov added a comment -

          ralf.gebhardt, this is a new feature.

          bar Alexander Barkov added a comment - ralf.gebhardt , this is a new feature.
          julien.fritsch Julien Fritsch made changes -
          Priority Major [ 3 ] Critical [ 2 ]

          People

            bar Alexander Barkov
            karlsson Anders Karlsson
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.