How to only check the time on datetime fields but ignore the date?

Question

I have a column that stores data in datetime format. I want to check for all instances where the time part of this column is not equal to 00:00:00:000 - the date does not matter.

Basically, if time() was a function, something like this:

SELECT  *
FROM    progen.DY
WHERE   TIME(DY_DATE) <> '00:00:00:000'

How do I go about doing this?

Solution 1

You only need a minor tweak on what you already have.

SELECT  *
FROM    progen.DY
WHERE   TIME(DY_DATE) <> '00:00:00:000'

Use CONVERT to change your DATETIME to a TIME.

SELECT  *
FROM    progen.DY
WHERE   CONVERT(TIME, DY_DATE) <> '00:00:00:000'

Solution 2

Use DATEDIFF and DATEADD to instead get the date part of the datetime. Compare the column against the date only, and it will return those rows that have a non-zero time.

The way this works is that we first calculate the difference (in days) between the epoch and the value. We add that number to the epoch to create a new datetime. Since the result of DATEDIFF is an integer, any time component gets rounded off.

SELECT *
FROM Table
WHERE DateColumn <> DATEADD(d, DATEDIFF(d, 0, DateColumn), 0)

The time function could then be implemented by the following, not that I recommend it for this specific scenario:

SELECT DATEDIFF(minute, DATEADD(d, DATEDIFF(d, 0, DateColumn), 0), DateColumn) as MinutesIntoDay,
    -- or, if you require higher precision
    DATEDIFF(second, DATEADD(d, DATEDIFF(d, 0, DateColumn), 0), DateColumn) as MinutesIntoDay
FROM Table

---

Edit: As mentioned in other answers, you can cast to DATE to achieve the same effect as DATEADD(d, DATEDIFF(d, 0, DateColumn), 0), which cleans up nicely. However, DATE was only added in SQL Server 2008, whereas the formula has compatibility back to at least SQL 2000. So if you need the backwards compatibility or are dealing with SQL CE, casting to DATE is unavailable.

Solution 3

Another way is to convert it to different datatype, eg

SELECT  *
FROM    progen.DY
WHERE   CAST(DY_DATE as float) - CAST(DY_DATE as int) > 0

• SQLFiddle Demo

Solution 4

I do this all the time when trying to see if a table's column should be turned into a date instead of a datetime, which is really the answer.

select *
from progen.dy
where cast(dy_date as Date) <> dy_date

the cast removes the time and datetime has higher precedence, so when compared, if the are unequal then it has a time value. Same thing could be done with a cast to time, with a bit of different syntax.

Solution 5

SELECT * 
FROM progen.DY 
WHERE CONVERT(TIME, DY_DATE - CONVERT(DATE, DY_DATE)) > '00:00'

View more solutions

Comments

[Michael A]

Are there any limitations / concerns with this method? It seems strange to me that this took so long to be proposed (based on other answers) if this is really the best way?

[Mikael Eriksson]

@Michael There is no limitations doing it like this, at least not compared to the other answers. The bad thing about applying functions on columns like that is that any indexes on that column can not be used and SQL Server has to do a table scan to figure out what rows you need.