How to Convert Varchar Dates (e.g., Sep-19) to Date Type in SQL?

Summary: Learn how to convert varchar date formats (like Sep-19) to a proper date type in MySQL, SQL Server, and MariaDB.
---

How to Convert Varchar Dates (e.g., Sep-19) to Date Type in SQL?

When your data includes date values stored as varchar strings, such as "Sep-19", you might face challenges in performing date comparisons and aggregations. This guide will guide you through converting varchar dates into proper date types in MySQL, SQL Server, and MariaDB to enable efficient querying and processing.

Understanding the Challenge

Date values stored as varchar or other non-date types can complicate SQL operations. For example, "Sep-19" can signify September 2019, but as a varchar, it's challenging to perform calculations like date differences or sorting.

Why Convert Varchar to Date?

Sorting: Date comparisons within varchar are string-based, leading to incorrect order.

Calculations: Date calculations, such as getting the difference or adding days, aren't feasible.

Functions: Date-specific functions (e.g., MONTH(), YEAR()) require dates in a proper format.

Conversion Strategies

MySQL

In MySQL, you can use the STR_TO_DATE() function to convert varchar to date.

[[See Video to Reveal this Text or Code Snippet]]

Explanation:

%b represents the abbreviated month name.

%y represents the two-digit year.

If your varchar dates are stored in a table column, you can directly apply this function in your SELECT statement:

[[See Video to Reveal this Text or Code Snippet]]

SQL Server

In SQL Server, the conversion can be managed by using the CONVERT() function along with a combination of string manipulation functions.

[[See Video to Reveal this Text or Code Snippet]]

Explanation:

01- prefix assumes the first day of the month to make the date string parseable.

Format 106 in CONVERT() translates the string to "dd-MMM-yy".

MariaDB

MariaDB, like MySQL, supports the STR_TO_DATE() function for this conversion.

[[See Video to Reveal this Text or Code Snippet]]

And similarly, when applied to a table column:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

Converting varchar dates to date types in SQL allows you to leverage the full potential of SQL's date functions and operations. By using built-in functions like STR_TO_DATE() in MySQL and MariaDB or CONVERT() in SQL Server, you can streamline your data processing workflows and ensure accurate date manipulations.

By following the methods discussed, you can handle varchar dates effectively, leading to cleaner, more efficient, and accurate SQL queries.