# TO\_DATE

Converts a string to a `DATE` or `DATETIME` value.

## Syntax

```
TO_DATE ( convert_from_value, convert_from_format )

```

## Arguments

* convert\_from\_value: A string specifying the value to convert from.
* convert\_from\_format: A format string, comprised of one or more [format specifiers](https://docs.singlestore.com/db/v9.1/reference/sql-reference/date-and-time-functions/to-char.md).

## Remarks

* `convert_from_format` may contain characters that are not format specifiers. These characters must appear in the same position in `convert_from_value`; otherwise, `TO_DATE` will return `NULL`.
* For failed conversions, the function throws an error.
* In `convert_from_value` and `convert_from_format` values, all punctuations can be used as separators ( **! " # $ % & ’ ( ) \* + , - . / : ; < = > ? @ \[ \ ] ^ \_ \` { ¦ } \~** ). The separator in `convert_from_value` may not match the seperator in `convert_from_format`.
* The function automatically fills the missing date parameters. The missing parameters take the following values:

  * **day**: first day of the month
  * **month**: the current month
  * **year**: the current year
* For `DATETIME` value conversion, fractional seconds are not supported.

## Examples

**Example 1**

The following examples demonstrate a typical call to `TO_DATE` where the string to be converted is specified in `MM/DD/YYYY` and `MM/DD/YYYY HH:MI:SS` formats.

```sql
SELECT TO_DATE('03/01/2019','MM/DD/YYYY') AS result;

```

```
+------------+
| result     |
+------------+
| 2019-03-01 |
+------------+
```

```sql
SELECT TO_DATE('03/01/2019 10:40:27','MM/DD/YYYY HH:MI:SS') AS result;

```

```
+---------------------+
| result              |
+---------------------+
| 2019-03-01 10:40:27 |
+---------------------+
1 row in set (0.00 sec)
```

**Example 2**

If day is passed as the string in `TO_DATE`, it returns the current month and year in `YYYY/MM/DD` format.

The following is the output when the command is run in April 2019.

```sql
SELECT TO_DATE('30','DD');

```

```output

+--------------------+
| TO_DATE('30','DD') |
+--------------------+
| 2019-04-30         |
+--------------------+
1 row in set (0.41 sec)

```

If year is passed as the string in `TO DATE`, it returns the specified year, current month and first day of the month:

The following is the output when the command is run in the month of April.

```sql
SELECT TO_DATE('2019','YYYY');

```

```output

+------------------------+
| TO_DATE('2019','YYYY') |
+------------------------+
| 2019-04-01             |
+------------------------+
1 row in set (0.00 sec)

```

If month is passed as the string in `TO_DATE`, it returns first day of the specified month of the current year:

The following is the output when the command is run in the year 2019.

```sql
SELECT TO_DATE('11','MM');

```

```output

+--------------------+
| TO_DATE('11','MM') |
+--------------------+
| 2019-11-01         |
+--------------------+
1 row in set (0.23 sec)

```

**Example 3**

The following example demonstrates another call to `TO_DATE`, where the string to be converted contains the spelling of the month. In this example, the string `The day is` is included in the same location in both arguments, allowing the function to successfully return a `DATE` value.

```sql
SELECT TO_DATE('The day is March 01, 2019','The day is MONTH DD, YYYY') AS Result;

```

```output

+------------+
| Result     |
+------------+
| 2019-03-01 |
+------------+

```

***

Modified at: May 31, 2023

Source: [/db/v9.1/reference/sql-reference/date-and-time-functions/to-date/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/date-and-time-functions/to-date/)

(An index of the documentation is available at /llms.txt)