SQL Date, String and Data Type Conversion Functions

Data often arrives messy. Dates are stored as text, names have stray spaces and odd capitalization, and numbers sit inside text columns. Until you fix these, sorting, filtering and math will give wrong answers.

Text dates are the classic trap. Sorted as text, '10-01' lands before '9-01', because the comparison goes character by character. Once converted to a real DATE, the order is correct and you can do date math as well.

Cleaning a Messy Table in One Query

Here is a raw_orders table where everything was imported as text.

SELECT INITCAP(TRIM(customer_name))              AS customer_name,
       CAST(order_date_text AS DATE)             AS order_date,
       amount_text::NUMERIC(8,2)                 AS amount,
       EXTRACT(MONTH FROM CAST(order_date_text AS DATE)) AS order_month
FROM raw_orders;

Output:

Code Explanation:

• TRIM strips the extra spaces around Riya’s name.
• INITCAP then capitalizes the first letter of each word and lowers the rest, so ARJUN MEHTA becomes Arjun Mehta.
• CAST(... AS DATE) turns the text into a true date, and ::NUMERIC(8,2) is PostgreSQL’s shorter way to convert text into a decimal number.
• EXTRACT(MONTH FROM ...) pulls the month number out of the converted date.

INITCAP exists in PostgreSQL but not in MySQL. In MySQL, you would build the same effect with UPPER and LOWER on parts of the string.

Functions Worth Knowing

Dates can be subtracted directly. In PostgreSQL, DATE '2026-03-18' - DATE '2026-03-15' gives 3, the number of days between them.