Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Compare time part of DateTime data type in SQL Server 2005

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))
like image 684
Miguel Angelo Avatar asked Jan 04 '10 17:01

Miguel Angelo


2 Answers

SELECT *
FROM Table1
WHERE DATEADD(day, -DATEDIFF(day, 0, MyDateField), MyDateField) > '12:30:50.400'
like image 116
LukeH Avatar answered Sep 18 '22 22:09

LukeH


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.

like image 22
marc_s Avatar answered Sep 19 '22 22:09

marc_s