Command guide

intermediate conversion MySQL MariaDB SQL Server PostgreSQL SQLite

Parse A Formatted String Into A Date

Convert a text string in a known format into a native DATE value using engine-specific parsing functions.

Created Apr 11, 2026 Last updated Apr 11, 2026 5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Parse a day/month/year formatted string into a DATE

15/04/2026 is unambiguous to a human but SQL engines need explicit format instructions because 15 cannot be a month. Each engine's parsing function reads the format mask left-to-right: day first, then month, then four-digit year. The result is a native DATE value — not a string — so subsequent operations like DATE_ADD or DATEDIFF work without further conversion.

MySQL MariaDB

Engine-specific syntax

Source table data

Setup

CREATE TABLE imports (id INT, date_text VARCHAR(20));

INSERT INTO
  imports (id, date_text)
VALUES
  (1, '15/04/2026'),
  (2, '01/01/2025');

Validated query

SQL

SELECT
  id,
  STR_TO_DATE (date_text, '%d/%m/%Y') AS parsed_date
FROM
  imports
ORDER BY
  id;

Expected result

id	parsed_date
1	2026-04-15
2	2025-01-01

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE imports (id INT, date_text VARCHAR(20));

INSERT INTO
  imports (id, date_text)
VALUES
  (1, '15/04/2026'),
  (2, '01/01/2025');

Validated query

SQL

SELECT
  id,
  CONVERT(DATE, date_text, 103) AS parsed_date
FROM
  imports
ORDER BY
  id;

Expected result

id	parsed_date
1	2026-04-15
2	2025-01-01

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE imports (id INT, date_text VARCHAR(20));

INSERT INTO
  imports (id, date_text)
VALUES
  (1, '15/04/2026'),
  (2, '01/01/2025');

Validated query

SQL

SELECT
  id,
  TO_DATE (date_text, 'DD/MM/YYYY') AS parsed_date
FROM
  imports
ORDER BY
  id;

Expected result

id	parsed_date
1	2026-04-15
2	2025-01-01

SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE imports (id INT, date_text VARCHAR(20));

INSERT INTO
  imports (id, date_text)
VALUES
  (1, '15/04/2026'),
  (2, '01/01/2025');

Validated query

SQL

SELECT
  id,
  date(
    substr (date_text, 7, 4) || '-' || substr (date_text, 4, 2) || '-' || substr (date_text, 1, 2)
  ) AS parsed_date
FROM
  imports
ORDER BY
  id;

Expected result

id	parsed_date
1	2026-04-15
2	2025-01-01

MySQL and MariaDB use STR_TO_DATE with % format codes; PostgreSQL uses TO_DATE with word-based format codes; SQL Server uses CONVERT with a numeric style code (103 = dd/mm/yyyy). All return the same DATE value serialized as YYYY-MM-DD.

Useful when

Where this command helps.

importing CSV data where dates arrive in day/month/year order rather than ISO format
parsing user-entered date strings before storing them as native DATE values

Explanation

What the command is doing.

When date values arrive as text in a non-ISO format such as 15/04/2026, they must be explicitly parsed before the database treats them as dates. MySQL and MariaDB use STR_TO_DATE(string, format) with %d/%m/%Y format codes. PostgreSQL uses TO_DATE(string, format) with DD/MM/YYYY format codes. SQL Server uses CONVERT(DATE, string, style) where style code 103 means dd/mm/yyyy. All approaches return a native DATE value that supports date arithmetic, comparison, and formatting.

Parse A Formatted String Into A Date

Parse a day/month/year formatted string into a DATE

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.