# TO\_TIMESTAMP

Converts a string to a `TIMESTAMP(6)` value.

## Syntax

```
TO_TIMESTAMP ( convert_from_value, convert_from_format )

```

## Arguments

* convert\_from\_value: String specifying the value to convert from.
* convert\_from\_format: 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` else, the function will return `NULL`.
* Format specifiers `YYYY` and `YY` returns the results in `YYYY` format.
* Format specifier `FFn` can accept any value from zero to nine decimal places.
* 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 separator in `convert_from_format`.

## Examples

**Example 1**

In the following example, the string `The date and time are` is included in the same location in both arguments to `TO_TIMESTAMP`, allowing the function to successfully return a `TIMESTAMP(6)` value. If this string were to be excluded from both arguments, the output would remain the same as shown below.

```sql
SELECT TO_TIMESTAMP('The date and time are 01/01/2018 2:30:15.123456',
    'The date and time are MM/DD/YYYY HH:MI:SS.FF6') AS result;

```

```output

+----------------------------+
| result                     |
+----------------------------+
| 2018-01-01 02:30:15.123456 |
+----------------------------+

```

**Example 2**

For Year, use either `YYYY`, `YY` or `RR` format specifier.

```sql
SELECT TO_TIMESTAMP('10-APR-2018 11:59:59','DD-MON-YYYY HH:MI:SS') AS Result;
SELECT TO_TIMESTAMP('10-APR-18 11:59:59','DD-MON-YY HH:MI:SS') AS Result;
SELECT TO_TIMESTAMP('10-APR-18 11:59:59','DD-MON-RR HH:MI:SS') AS Result;

```

```output

+---------------------+
| Result              |
+---------------------+
| 2018-04-10 11:59:59 |
+---------------------+
1 row in set (0.24 sec)

```

**Example 3**

The following table lists few ways of using format specifier `FF` in the `TO_TIMESTAMP` function, along with their output:

| Call to`TO_TIMESTAMP`                                            | Result                       |
| ---------------------------------------------------------------- | ---------------------------- |
| `('10-APR-18 11:59:59 PM','DD-MON-RR HH:MI:SS.FF PM')`           | `2018-04-10 23:59:59`        |
| `('10-APR-18 11:59:59 PM','DD-MON-RR HH:MI:SS.FF1 PM')`          | `2018-04-10 23:59:59`        |
| `('10-APR-18 11:59:59 PM','DD-MON-RR HH:MI:SS.FF9 PM')`          | `2018-04-10 23:59:59`        |
| `('10-APR-18 11:59:59 PM','DD-MON-RR HH:MI:SS FF PM')`           | `2018-04-10 23:59:59.000000` |
| `('10-APR-18 11:59:59 PM','DD-MON-RR HH:MI:SS FF1 PM')`          | `2018-04-10 23:59:59.000000` |
| `('10-APR-18 11:59:59. PM','DD-MON-RR HH:MI:SS.FF PM')`          | `2018-04-10 23:59:59.000000` |
| `('10-APR-18 11:59:59.000000000 PM','DD-MON-RR HH:MI:SS.FF PM')` | `2018-04-10 23:59:59.000000` |
| `('11.59.59.1234567892018','HH.MI.SS.FFYYYY')`                   | `2018-04-01 11:59:59.123456` |
| `('04-10 11:59:59 123 2018','MM-DD HH:MI:SS FF8 YYYY')`          | `2018-04-10 11:59:59.123000` |
| `('11.59.59.1234567890','HH.MI.SS.FF')`                          | `2019-04-01 11:59:59.123456` |

If input fractional is larger than FF format, then it throws an error.

```sql
SELECT TO_TIMESTAMP('04-10 11:59:59 123 2018','MM-DD HH:MI:SS FF1 YYYY');

```

```output

ERROR 2353 ER_STR_TO_DATE_CONVERSION: More digits than expected for fractional seconds

```

**Example 4**

The following example shows the usage of few valid punctuations that can be used as separators in `convert_from_value` and `convert_from_format`:

```sql
('10/APR}18 11.59.59.0 PM','DD-MON-RR HH:MI:SS.FF PM')
('10)APR{18 11.59.59.00 PM','DD-MON-RR HH@MI[SS]FF PM')
('10&APR|18 11.59.59.000000000 PM','DD-MON-RR HH:MI:SS~FF PM')
('10$APR`18 11<59>59=00 PM','DD-MON-RR HH:MI:SS~FF PM')
('10.APR.18', 'DD-MON-RR')
('10/APR/18', 'DD-MON-RR')
('10%APR/18', 'DD#MON!RR')

```

***

Modified at: May 31, 2023

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

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