CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Michael A
Michael A

Reputation: 9900

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

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?

Upvotes: 16

Views: 26232

Answers (5)

Mikael Eriksson
Mikael Eriksson

Reputation: 138980

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'

Upvotes: 22

Mitch
Mitch

Reputation: 22281

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.

Upvotes: 3

jmoreno
jmoreno

Reputation: 13571

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.

Upvotes: 5

John Woo
John Woo

Reputation: 263803

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

Upvotes: 3

Ravindra Gullapalli
Ravindra Gullapalli

Reputation: 9178

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

Upvotes: 2

Related Questions