timestamp date filter does not work

[collierd]

Hello

I have a column called LastUpdated defined as timestamp

If I select from the table it displays as (e.g.) 2025-08-27T10:50:31.610+00:00

How do I filter on this without having to be specific with the year, month, day, ...

This does not work:

select * from available

where LastUpdated = '2025-08-27T10:50:31.610+00:00'

Feel like there's a better approach than

select * from available

where year(LastUpdated) = 2025

and month(LastUpdated) = 8

and day(LastUpdated) = 27

etc.

Thanks

[WiliamRosa]

Hi @collierd, Try this:

WHERE CAST(LastUpdated AS DATE) = DATE '2025-08-27';

[collierd]

Thanks

That's useful but I also need the time

[szymon_dybczak · Accepted solution]

Hi @collierd ,

You have many options here. You can use 

1. Date Truncation - which returns timestamp truncated to the unit (you can use day, quarter, month etc)

%sql
SELECT 
   date_trunc('day', LastUpdated),
   LastUpdated
FROM (
  SELECT current_timestamp() as LastUpdated 
) src
WHERE date_trunc('day', LastUpdated) = '2025-08-27'

  2. You can use date casting 

SELECT 
   CAST(LastUpdated AS DATE) As lastUpdatedCasted,
   LastUpdated
FROM (
  SELECT current_timestamp() as LastUpdated 
) src
WHERE CAST(LastUpdated AS DATE) = '2025-08-27'

3. Range filtering

SELECT    
   LastUpdated
FROM (
  SELECT current_timestamp() as LastUpdated 
) src
WHERE LastUpdated >= '2025-08-27' 
  AND LastUpdated < '2025-08-28'

4. Format Matching

%sql
SELECT 
   date_format(LastUpdated, 'yyyy-MM'),
   LastUpdated
FROM (
  SELECT current_timestamp() as LastUpdated 
) src
WHERE date_format(LastUpdated, 'yyyy-MM') = '2025-08'

[collierd]

Thanks

date_trunc is the moset useful here as it includes the time

date_format only references the date part - although that is useful

[Pilsner · Accepted solution]

Hello @collierd ,

The way I would tackle this would involve data time specifiers. Because your value is likely stored as a timestamp which you can see via the catalog explorer, you cannot compare it to a string value such as "2025-08-27T10:50:31.610+00:00".

To get around this you can use date time specifiers to convert the string into a timestamp using the below code:

 to_timestamp('2025-08-27T10:50:31.610+00:00', "yyyy-MM-dd'T'HH:mm:ss.SSSXXX")

If your curious about these date time specifiers there is a fill list of them available here:
https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-datetime-pattern

The basic idea is that you select the relevant specifier (i.e "y" for year) then repeat this character to match the length of the string (so for a 4 digit year you get "yyyy").

I hope this helps but feel free to ask any further questions.

Regards - Pilsner

[collierd]

Thanks

to_timestamp works well

[Pilsner]

Glad I could help @collierd!

@szymon_dybczak I wasn't aware of the date_trunc function. I like the fact that you can pass simple terms, such as "day" in as a parameter, it makes it easy to read. Thanks!