Sometimes a single column arrives with mixed date formats. If a hire-date column holds both 2007-06-28T00:00:00.000+00:00 and 12/15/2019T00:00:00.000+00:00, converting it to a date in Oracle Analytics (OAC) fails for one of them. This post shows how to normalize the values with Replace (regular expressions) in the dataset editor and then convert them to dates.
I first wrote this up in Notion in September 2024 and re-checked and corrected it against Oracle’s documentation in October 2026. The original screenshots showed the OAC interface from around 2020, so I left them out. The current OAC layout and menu names differ, so check menu locations in your version; the features themselves (regular expressions in Replace, Convert to Date) are still in the current documentation.
What was wrong with the original approach
The original replaced only the MM/dd/yyyy part and then converted with the custom format yyyy-MM-dd'T'HH:mm:ss.SSS+SS:SS. That format is a workaround that reads the trailing time zone +00:00 as a seconds field (SS), so any value with an offset other than +00:00 is misread. Its regex, (\d{1,2})/(\d{1,2})/(\d{4}) → $3-$1-$2, also turns 6/5/2019 into 2019-6-5, which has the wrong number of digits.
If you only need the date, it’s simpler and safer to cut off the time and time zone first, pad month and day to two digits, and then reorder to yyyy-MM-dd.
Four Replace steps
In the dataset editor, select the date column, choose Replace, turn on “Use regular expression”, and add these four steps in order. OAC’s Replace supports PCRE, and the new string refers to capture groups as $1, $2.
| Step | String to replace (regex) | New string | What it does |
|---|---|---|---|
| 1 | T.*$ | (empty) | Removes the time and time zone after T |
| 2 | ^(\d)/ | 0$1/ | Pads a one-digit month |
| 3 | /(\d)/ | /0$1/ | Pads a one-digit day |
| 4 | ^(\d{2})/(\d{2})/(\d{4})$ | $3-$1-$2 | MM/dd/yyyy → yyyy-MM-dd |
The results are below. Values already in yyyy-MM-dd only lose their time in step 1 and aren’t touched by the other steps.
| Original value | Result |
|---|---|
2007-06-28T00:00:00.000+00:00 | 2007-06-28 |
12/15/2019T00:00:00.000+00:00 | 2019-12-15 |
6/5/2019T00:00:00.000+00:00 | 2019-06-05 |
11/2/2020T00:00:00.000+00:00 | 2020-11-02 |
I verified these results by running the same expressions with Python’s re (which uses \1 instead of $1). In OAC, check the preview after each step before adding the next one.
Convert to a date
Select the column, choose Convert to Date, and pick a Source Format matching yyyy-MM-dd or enter it as a custom format. Add the step and save the dataset to apply it.
Note: These expressions assume the slash format is month/day/year. If day/month/year values are mixed in, you can’t tell them apart from the values alone, so confirm the format in the source system. If you need the time or time zone for analysis, don’t cut them off in step 1; decide on a time zone convention first.
Summary
Normalize a mixed-format date column with Replace (regular expressions), then convert it with Convert to Date. Cutting off what you don’t need and padding the digits handles unexpected values better than a workaround format that reads the time zone as another field.
