# BSON\_EXTRACT\_DATETIME

Extracts the value for the specified key from the BSON data, converts it to a SingleStore `DATETIME(6)` type, and returns the value.

This function accepts the following BSON types:

* Datetime
* Timestamp
* ObjectId

> **📝 Note**: The date and time range supported by SingleStore `DATETIME(6)` type differs from BSON datetime type. Refer to [BSON Date Type](https://www.mongodb.com/docs/manual/reference/bson-types/#date) and [SingleStore Time and Date Type](https://docs.singlestore.com/cloud/reference/sql-reference/data-types/time-and-date/) for more information.

## Syntax

```
BSON_EXTRACT_DATETIME(<input> [, path_key_1, path_key_2, ..., path_key_n])
```

## Arguments

* `<input>`: A supported BSON type value, a valid BSON document, or an expression that evaluates to a valid BSON value or document.
* `<path>`: A comma-separated list of dictionary keys or array positions (zero-based index).

## Return Type

SingleStore `DATETIME(6)` type.

## Remarks

* If the `<path>` argument is not specified and the `<input>` is a BSON value of type datetime, timestamp or ObjectId, the `<input>` is converted to its equivalent SingleStore `DATETIME(6)` type. Otherwise, `NULL` is returned.
* If the value at the specified `<path>` is not a supported BSON type or the specified `<path>` does not exist, `NULL` is returned.

## Examples

The following examples extract datetime types:

```sql
SELECT BSON_EXTRACT_DATETIME('{"$date":"1234-05-28T21:35:22.567Z"}') AS Result;

```

```output

+----------------------------+
| Result                     |
+----------------------------+
| 1234-05-28 21:35:22.567000 |
+----------------------------+
```

```sql
SELECT BSON_EXTRACT_DATETIME('{"$oid":"0a01579f5d884c1735b882be"}') AS Result;

```

```output

+----------------------------+
| Result                     |
+----------------------------+
| 1975-04-27 19:48:47.000000 |
+----------------------------+
```

```sql
SELECT BSON_EXTRACT_DATETIME('{"$timestamp":{"i":8,"t":1679525146}}') AS Result;

```

```output

+----------------------------+
| Result                     |
+----------------------------+
| 2023-03-22 22:45:46.000000 |
+----------------------------+
```

***

Modified at: April 2, 2024

Source: [/cloud/reference/sql-reference/bson-functions/bson-extract-datetime/](https://docs.singlestore.com/cloud/reference/sql-reference/bson-functions/bson-extract-datetime/)

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