sqlsql-serverdatetime

Find DATETIME values with a non-00:00:00 time


I have a column in SQL Server with DATETIME values. Most rows appear to have no time component, (eg, 2025-06-17 00:00:00.000), but I want to double check this by searching for any rows where the time is not exactly midnight.

So for example, given this data:

ID StartDate
1 2025-06-11 00:00:00.000
2 2025-06-12 00:00:00.000
3 2025-06-13 00:03:30.000
4 2025-06-14 00:00:01.023
5 2025-06-15 00:00:00.000

I want a query that only returns rows with a non-zero time:

ID StartDate
3 2025-06-13 00:03:30.000
4 2025-06-14 00:00:01.023

How can I filter out all rows where the time part is exactly 00:00:00.000?


Solution

  • Comparing StartDate to StartDate casted as date:

    SELECT *
    FROM Timetable
    WHERE StartDate != CAST(StartDate AS DATE);
    

    db<>fiddle Demo