Compare time part of DateTime data type in SQL Server 2005

Question

How can I compare only the time portion of a DateTime data type in SQL Server 2005. For example, I want to get all records in which 'MyDate' field is after a specific time. The following example is a very long and probably not fast way of doing this. I want all date in wich time is greater than 12:30:50.400

SELECT *
FROM   Table1
WHERE      ((DATEPART(hour, MyDateField) = 12) AND (DATEPART(minute, MyDateField) = 30) AND (DATEPART(second, MyDateField) = 50) AND (DATEPART(millisecond, MyDateField) > 400))
        OR ((DATEPART(hour, MyDateField) = 12) AND (DATEPART(minute, MyDateField) = 30) AND (DATEPART(second, MyDateField) > 50))
        OR ((DATEPART(hour, MyDateField) = 12) AND (DATEPART(minute, MyDateField) > 30))
        OR ((DATEPART(hour, MyDateField) > 12))

Answer 1

How about this?

SELECT (fields)
FROM dbo.YourTable
WHERE DATEPART(HOUR, MyDate) >= 12
  AND DATEPART(MINUTE, MyDate) >= 23
  AND DATEPART(SECOND, MyDate) >= 45

The hour is given in the 24-hour format, e.g. 12 means 12 hour noon, 15 means 3pm.

DATEPART also has "parts" for minutes, seconds and so forth. You can of course use as many of those date parts in your WHERE clause as you like.

Answer 2

convert(varchar(10),MyDate,108)

This gives you a time string of format HH:MM:SS.

You can then do any comparison you want with it

Answer 3

What I wanted to do is to extract the time portion of a DateTime data type, and compare it. I found a way to extract the date portion here in StackOverflow. If I have the date part alone, it is just subtract the date from the source DateTime:

datePortion = DATEADD(day, DATEDIFF(day,0, sourceDate), 0)
timePortion = DATEDIFF(millisecond, datePortion, sourceDate)

so the macro to extract the time portion in SQL Server 2005 is:

f(x) = DATEDIFF(millisecond, DATEADD(day, DATEDIFF(day,0, sourceDate), 0), sourceDate)

Now the query to compare the time portion of a DateTime field, with 12:30:50.400 is:

SELECT *
FROM   Table1
WHERE
        DATEDIFF(millisecond, DATEADD(day, DATEDIFF(day, 0, DateTimeField), 0), DateTimeField)
        >
        DATEDIFF(millisecond, DATEADD(day, DATEDIFF(day, 0, '1900-01-01T12:30:50.400'), 0), '1900-01-01T12:30:50.400')

I have tested this query against other kinds of queries, including using subtraction operator ('-'), and CONVERT. The execution plan comparison indicates that this is the fastest method to do this. I also tested the real times of query execution... there is no noticeable fastest method.

Answer 4

Others have posted alternative methods of forming the query, but I want to respond to something you say in the first paragraph:

The following example is a very long and probably not fast way of doing this.

Performance-wise, it doesn't really matter if you have one DATEPART or 10 DATEPARTS in there; the fact that you have even one DATEPART is what's going to hurt performance.

If you need to compare on time but not date, then Lasse's comment is right on the money - you need to normalize into separate date/time columns. In fact, SQL 2008 introduces date and time types and recommends that you use them instead of datetime. You don't have this in SQL 2005, but you can work around it with, say, a ticks field for time-of-day, and a check constraint that forces all the dateparts of the date column to zero.

If you later need to perform filters on the full date+time, it is easy to create a computed column, and if performance is an issue for those, you can persist that column and create an index on it.

Answer 5

SELECT *
FROM Table1
WHERE DATEADD(day, -DATEDIFF(day, 0, MyDateField), MyDateField) > '12:30:50.400'