Convert Date from DD-Mon-YY to dd.mm.yy

To convert a date from the DD-Mon-YY format to dd.mm.yy in SQL, you must parse the string into a proper date object and then reformat it. In MySQL, use STR_TO_DATE() to parse the input and DATE_FORMAT() to output the target style. For PostgreSQL, utilize TO_DATE() for parsing and TO_CHAR() for formatting, ensuring you handle the two-digit year ambiguity correctly during the conversion process.

Understanding Date Format Conversion

Date manipulation is a common requirement in data engineering and application development. The source format DD-Mon-YY typically represents a day (two digits), a three-letter month abbreviation (like Jan, Feb), and a two-digit year. The target format dd.mm.yy requires a numeric day, a dot separator, a numeric month, another dot, and a two-digit year. This transformation is not merely cosmetic; it often prepares data for systems that require strict ISO-like or localized date standards for sorting, indexing, or display. When working with SQL databases, dates are rarely stored as strings unless they are legacy fields or imported data. If your column is already a DATE or TIMESTAMP type, no conversion is needed for storage, but you may still need to format it for output. However, if the data is stored as a VARCHAR or TEXT field containing values like "01-Jan-23", you must explicitly instruct the database engine to interpret these characters. The core challenge lies in mapping the textual month name to its numeric equivalent and handling the century for two-digit years.

MySQL Implementation

MySQL provides robust functions for this specific task using STR_TO_DATE() and DATE_FORMAT(). The first function converts the string into an internal date representation, while the second formats that internal value back into a string. Consider a table named orders with a column order_date_str containing values like "15-Dec-99". To convert this, you chain the functions together. The STR_TO_DATE function takes the string and a format specifier. The specifier %d-%b-%y tells MySQL to expect a two-digit day, a dash, a abbreviated month name, another dash, and a two-digit year.

SELECT 
  DATE_FORMAT(
    STR_TO_DATE(order_date_str, '%d-%b-%y'), 
    '%d.%m.%y'
  ) AS converted_date
FROM orders;

The output of DATE_FORMAT uses %d for the day, %m for the zero-padded month number, and %y for the two-digit year. Note that %M would give the full month name, which is incorrect for our target. This approach ensures that "Jan" becomes "01", "Feb" becomes "02", and so on. For updating the actual table data rather than just selecting it, you can use an UPDATE statement. Be cautious when updating large tables, as this operation can be resource-intensive. Always test on a subset of data first.

UPDATE orders
SET order_date_str = DATE_FORMAT(
  STR_TO_DATE(order_date_str, '%d-%b-%y'), 
  '%d.%m.%y'
)
WHERE order_date_str IS NOT NULL;

This method is reliable because MySQL handles the locale-independent parsing of standard English month abbreviations by default. If your data contains non-English month names, you may need to adjust the session language or use custom mapping logic.

PostgreSQL Implementation

PostgreSQL approaches date conversion with TO_DATE() and TO_CHAR(). Similar to MySQL, these functions separate the parsing and formatting stages. The syntax is slightly different, requiring explicit casting in some contexts if the result type needs to be enforced. Assuming a table transactions with a text column trans_date, the query looks like this:

SELECT 
  TO_CHAR(
    TO_DATE(trans_date, 'DD-Mon-YY'), 
    'DD.MM.YY'
  ) AS formatted_date
FROM transactions;

In TO_DATE, the format mask 'DD-Mon-YY' is case-sensitive for the month part in some versions, but generally accepts standard abbreviations. The 'Mon' specifier matches "Jan", "Feb", etc. The output mask 'DD.MM.YY' produces the desired dot-separated numeric format. One critical difference in PostgreSQL is how it handles two-digit years. By default, PostgreSQL interprets two-digit years according to the "2020 cutoff" rule. Years 00-49 are treated as 2000-2049, and 50-99 as 1950-1999. This behavior is controlled by the datestyle configuration parameter. If your business logic requires a different cutoff (e.g., 1970), you should verify or set this parameter in your session or database configuration. If you need to store the result back into a date column, you might cast the result:

UPDATE transactions
SET trans_date = TO_DATE(trans_date, 'DD-Mon-YY')::DATE;

However, since the target format dd.mm.yy is a string representation, keeping it as text is appropriate if the downstream system expects that exact visual format. If you convert it to a DATE type, the dots will disappear, as dates do not have a fixed string format internally.

Handling Edge Cases and Errors

Data quality issues often arise during conversion. Invalid month names, such as "Xyz", will cause the conversion functions to return NULL or raise an error, depending on the database settings. It is prudent to filter out bad records before attempting conversion. You can use conditional logic to identify rows where the conversion fails. In MySQL, you can check if STR_TO_DATE returns NULL. In PostgreSQL, similar checks apply. Cleaning the data beforehand prevents silent failures where valid-looking dates are lost due to minor typos. Additionally, ensure that the source data consistently uses dashes (-) as separators. If some records use slashes (/) or spaces, you may need to preprocess the string using REPLACE() or regular expressions before applying the date conversion functions. Linux Bash: Convert Date-Time Formats with Ease provides complementary techniques for handling these transformations at the operating system level, which can be useful for preprocessing files before database import. Similarly, understanding how to manipulate date outputs in Linux environments can help automate the cleaning pipeline.

Frequently Asked Questions

What happens if the month abbreviation is in a different language?

Standard SQL functions rely on the server's locale settings for month names. If your data uses Spanish or French months, you must either change the session locale or manually map the names using CASE statements before conversion.

Can I convert the date directly without parsing it first?

No, SQL engines cannot guess the structure of a string. You must explicitly define the input format using the parser function (STR_TO_DATE or TO_DATE) to establish the correct internal date object.

How does PostgreSQL handle years between 1950 and 2049?

PostgreSQL uses a 50-year cutoff by default. Years 00-49 become 2000-2049, while 50-99 become 1950-1999. You can adjust this behavior via the datestyle setting if your historical data requires a different interpretation.

Why did my date turn into NULL after conversion?

NULL usually indicates a mismatch between the input string and the format mask. Check for extra spaces, incorrect separators (like slashes instead of dashes), or invalid month names that do not match the expected abbreviations.

Is it better to store dates as strings or date types?

It is always better to store dates as DATE or TIMESTAMP types for efficient querying and sorting. Use string conversion only for display purposes or when dealing with legacy data that must be cleaned before migration.