Convert 3 letter month column into a month number in Databricks SQL

Question

I have a column with month - Jan, Feb, Mar, Apr etc.

I would like to query the data and order the resultset but having a month name makes in difficult.

How can I change the 3 letter month to a month number so that I can use > or < operator and order by month number.

I am writing these queries on Azure Databricks SQL.

Answer 1

You can use the TO_DATE function to convert a string to a date in Databricks SQL. Then use the month() function to convert the month into month number.

SELECT month(to_date(<column-name>, 'MMM')) AS month_number from <table-name>

The query will return the month number for the month in the column-name. The to_date function converts the <column-name> value to a date format using the format 'MMM', which specifies that the input string is a three-letter month abbreviation. The resulting date is then passed to the month function, which extracts the month number from the date.

Answer 2

You can use this code to convert the month name to month number:

SELECT MONTH(STR_TO_DATE(month_name, '%b')) AS month_number
FROM your_table