10

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 MyDateField is after a specific time. The following example is a very long and probably not fast way of doing this. I want all dates where MyDateField 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))
Jason
  • 43
  • 6
Miguel Angelo
  • 23,796
  • 16
  • 59
  • 82
  • Note that if you actually need to process the date and the time separately, you're effectively breaking normalization rules. A DateTime value is a specific point in time on a date. A Date and Time is a multi-valued field, which should've been broken up. The reason your code runs slow is that you've not normalized it correctly. – Lasse V. Karlsen Jan 04 '10 at 18:26
  • @Lasse - how far would you take this? Surely you don't store: day, month, year, hour, minute, weekday, weekend, quarter, week in separate fields instead of just storing the date. – JeffO Jan 04 '10 at 18:46
  • @Lasse - Of course I could separate a DateTime field in Date and Time fields. But then I would have the same problem to compare both date and time together. – Miguel Angelo Jan 04 '10 at 19:04
  • @Miguel: You could easily handle that comparison with a computed column, which you could persist and index for performance if necessary. Therefore I agree with Lasse. – Aaronaught Jan 04 '10 at 20:05

6 Answers6

14
SELECT *
FROM Table1
WHERE DATEADD(day, -DATEDIFF(day, 0, MyDateField), MyDateField) > '12:30:50.400'
LukeH
  • 263,068
  • 57
  • 365
  • 409
  • 1
    I think this is the best solution so far. It is clear, and easy to understand. Also it is the fastest solution regarding only the query, not DB structure. – Miguel Angelo Jan 05 '10 at 13:33
5

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • If midday is the cut-off then the logic should probably be either `hour >= 12` or maybe `hour > 12 or (hour = 12 and millisecond > 0)`. – LukeH Jan 04 '10 at 17:27
  • But I also want to consider the other parts of the time part, not only the hour. – Miguel Angelo Jan 04 '10 at 17:46
  • I have tested this, but it does not get records wich time is 13:00:00.000, replacing ANDs with ORs also does not solve the problem. The only solution is a combination of ANDs and ORs, but that would make the query so long... – Miguel Angelo Jan 04 '10 at 17:56
  • @Miguel: can you show us the query you've used?? Update your original question with the additional info – marc_s Jan 04 '10 at 18:03
  • 3
    I don't think that's going to work. That query will match 12:30:48 PM but not 2:00:00 PM, because the minute part doesn't match the criteria. Too bad you don't get the `time` type in SQL '05... – Aaronaught Jan 04 '10 at 20:03
  • @Aaron: true, that is an issue - you're absolutely right..... so I guess it's get a bit hairy if you want to compare those things down to the millisecond..... SQL Server 2008 would have such a nice **TIME** datatype!! – marc_s Jan 04 '10 at 20:45
1

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.

Community
  • 1
  • 1
Miguel Angelo
  • 23,796
  • 16
  • 59
  • 82
  • For tidying up the query, as opposed to improving performance, this is a pretty good solution if you toss it into a UDF. That way you can write it as `WHERE dbo.GetTime(MyDateField) >= @SomeTime`. – Aaronaught Jan 04 '10 at 21:25
  • At the begining I was wondering if I could get more performance only changing the query. It is possible, but not that much, less than 2%. I was unable to find the function GetTime. – Miguel Angelo Jan 04 '10 at 21:54
1

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.

Aaronaught
  • 120,909
  • 25
  • 266
  • 342
  • You are completelly correct! =) I asked the question, because I wanted to know how to solve it, the way it is, without changing the DB. In fact all QUERY-ONLY solutions are the same speed order, all are going to be slow anyway... I tested it right now... but using DATEADD and DATEDIFF is the best among the worst. – Miguel Angelo Jan 04 '10 at 20:45
0

Here's the SQL to match date with time:

to match only time:

AND CAST(RIGHT(CONVERT(VARCHAR, YOURDATE, 100), 7) as time) >
CAST(RIGHT(CONVERT(VARCHAR, GETDATE(), 100), 7) as time)

to match date with time:

CAST(YOURDATE AS DATE)=CAST(GETDATE() AS DATE)
AND CAST(RIGHT(CONVERT(VARCHAR, YOURDATE, 100), 7) as time) >
CAST(RIGHT(CONVERT(VARCHAR, GETDATE(), 100), 7) as time)
Bill Tür stands with Ukraine
  • 3,425
  • 30
  • 38
  • 48
errorfo7nd
  • 1
-1

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

zzawaideh
  • 2,021
  • 1
  • 17
  • 25
  • The time part also encloses the milliseconds... is there a format that also returns the full time part? What I am looking for, is a way to exclude the Date part leaving only the time. – Miguel Angelo Jan 04 '10 at 17:50