> ## Documentation Index
> Fetch the complete documentation index at: https://docs.e6data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Date and time functions

> Temporal operations depend on the input type and session time zone. DATE is a calendar value; TIMESTAMP and TIMESTAMP_NTZ have different zone semantics. The examples use date_dim a

ReferenceApplies to: Unified engine deployments

Temporal operations depend on the input type and session time zone. DATE is a calendar value; TIMESTAMP and TIMESTAMP\_NTZ have different zone semantics. The examples use date\_dim and UTC.

## Extracting date fields

| Form | Returns | Meaning |
| - | - | - |
| `date_part('year', value)` | Numeric | Calendar year |
| `date_part('month', value)` | Numeric | Month number |
| `date_part('day', value)` | Numeric | Day of month |
| `date_part(field, value)` | Numeric | A named temporal field |

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT d_date,
       DATE_PART('year', d_date) AS year_number,
       DATE_PART('month', d_date) AS month_number,
       DATE_PART('day', d_date) AS day_number
FROM demo.tpcds.date_dim
ORDER BY d_date_sk
LIMIT 5;
```

EXTRACT currently lowers to an unregistered function. Use DATE\_PART for these fields.

## Truncation and conversion

The runnable truncation example uses TIMESTAMP\_NTZ. Zone-aware DATE\_TRUNC can require the unavailable ICU extension in Native execution.

`date_trunc(unit, timestamp)` returns a timestamp truncated to the named unit. CAST changes a supported temporal representation. Parsing or formatting text requires a function and format accepted by the current engine; format-token languages differ across SQL systems.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT d_date,
       CAST(d_date AS TIMESTAMP_NTZ) AS midnight,
       DATE_TRUNC('month', CAST(d_date AS TIMESTAMP_NTZ)) AS month_start
FROM demo.tpcds.date_dim
ORDER BY d_date_sk
LIMIT 5;
```

## Current date and time

CURRENT\_DATE and CURRENT\_TIMESTAMP use session semantics. Their values change over time, so examples should check their types and relationship to the session rather than a fixed date.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT CURRENT_DATE AS today, CURRENT_TIMESTAMP AS now;
```

## Intervals

An interval represents a duration or calendar offset. Calendar months differ in length, and daylight-saving transitions can affect zone-aware arithmetic. Validate the supported interval form and unit for the expression and backend. Use explicit casts to avoid mixing text with timestamps implicitly.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT DATE '2024-01-15' AS original_day,
       DATE '2024-01-15' + INTERVAL '1' DAY AS following_day;
```

Changing `spark.sql.session.timeZone` affects subsequent operations in the same session. A zone can be recognized by the session yet need an installed backend time-zone extension for execution beyond UTC.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.