Convert timestamp to date in Oracle SQL

sql

timestamp-conversion

date-formatting

oracle-sql

byAlex Kataev·Jan 23, 2025

The Oracle SQL TRUNC function trims the time part of a timestamp:

-- If James Bond was a SQL function, he'd be 'TRUNC', because he likes his timestamps shaken, not stirred.
SELECT TRUNC(your_timestamp_column) FROM your_table;

For a more versatile approach, use the CAST function, it converts a timestamp to a date:

-- 'CAST' it to the winds, they said. And so I did...
SELECT CAST(your_timestamp_column AS DATE) FROM your_table;

Note the difference - while TRUNC removes the temporal segment, CAST retains this as midnight in the resultant date.

TimeZone handling: Don't get lost in time

If your timestamps have timezone data, include timezone specification in your conversion to avoid temporal distortion:

-- There's no place like UTC, there's no place like UTC...
SELECT CAST(your_timestamp_column AT TIME ZONE 'UTC' AS DATE) FROM your_table;

A conversion conscious of timezone ensures the time component aligns as expected.

Edge cases to be alert, like a midnight snacks run

Unforeseen elements can lead to befuddling results. Stay clear by remaining alert to:

Daylight savings: Changing the clocks

Timestamps around daylight savings shifts can feel like falling down a rabbit hole. Be aware for smooth conversion:

SELECT CAST(your_daylight_savings_timestamp AS DATE) FROM your_table;

Leap seconds: hop, skip, jump

Oracle's date type overlooks leap seconds. So timestamps on these need individual attention:

SELECT CAST(your_skip_a_beat_timestamp AS DATE) FROM your_table;

Precision dip: lose no second

When in need of exact time details, remember, casting to date loses fractional seconds:

-- In a world of approximations, be the precision you want to see.
SELECT CAST(your_high_precision_timestamp AS DATE) FROM your_table;

Query best practices: Your route to SQL rockstardom

When jaunting down the date and timestamp road, remember to:

Pick explicit time zones when dealing with TIMESTAMP WITH TIME ZONE.
Pause before TRUNC - using it on indexed columns can cripple your query's performance.
Always think your function use through for its performance impacts.

Extra scenarios: Prepare for the SQL apocalypse

Let's consider more scenarios where you convert timestamps to dates:

NULLS: The phantom menace

NULL entries may slip unseen into your results:

-- Only the NULLs you're looking for...
SELECT CAST(your_timestamp_column AS DATE) FROM your_table WHERE your_timestamp_column IS NOT NULL;

Formatting Dates : The pretty printers

Control your output format with TO_CHAR:

-- Once I was an ugly timestamp, but since 'TO_CHAR', I've been a swoon-worthy date.
SELECT TO_CHAR(CAST(your_timestamp_column AS DATE), 'YYYY-MM-DD') FROM your_table;

Interval calculations : Time travel made easy

Calculate date intervals using timestamps:

-- How long has it been since we lost saw each other? Let me calculate...
SELECT CAST(your_timestamp_column AS DATE) - CAST(your_other_timestamp_column AS DATE) FROM your_table;