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?

Answer 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'