CODEWITHSUNDEEP
sqlsql-serverdatetime
thursdaysgeek
thursdaysgeek

Reputation: 7936

How to get records after a certain time using SQL datetime field

If I have a datetime field, how do I get just records created later than a certain time, ignoring the date altogether?

It's a logging table, it tells when people are connecting and doing something in our application. I want to find out how often people are on later than 5pm.

(Sorry - it is SQL Server. But this could be useful for other people for other databases)

Upvotes: 21

Views: 95289

Answers (8)

Luke Bennett
Luke Bennett

Reputation: 32896

For SQL Server:

SELECT * FROM myTable WHERE DATEPART(hh, myDateField) > 17

See http://msdn.microsoft.com/en-us/library/aa258265(SQL.80).aspx.

Upvotes: 20

Zaxxon
Zaxxon

Reputation: 667

For MSSQL use the CONVERT method:


DECLARE @TempDate   datetime = '1/2/2016 6:28:03 AM'
SELECT  
    @TempDate as PassedInDate,  
    CASE 
        WHEN CONVERT(nvarchar(30), @TempDate, 108) < '06:30:00' then 'Before 6:30am'
        ELSE 'On or after  6:30am'
    END,
    CASE 
        WHEN CONVERT(nvarchar(30), @TempDate, 108) >= '10:30:00' then 'On or after 10:30am'
        ELSE 'Before 10:30am'
    END

Upvotes: 1

thursdaysgeek
thursdaysgeek

Reputation: 7936

Ok, I've got it.

select myfield1, 
       myfield2, 
       mydatefield
  from mytable
 where datename(hour, mydatefield) > 17

This will get me records with a mydatefield with a time later than 5pm.

Upvotes: 0

Jonathan Leffler
Jonathan Leffler

Reputation: 753585

In Informix, assuming that you use a DATETIME YEAR TO SECOND field to hold the full date, you'd write:

WHERE EXTEND(dt_column, HOUR TO SECOND) > DATETIME(17:00:00) HOUR TO SECOND

'EXTEND' can indeed contract the set of fields (as well as extend it, as the name suggests).

As Thilo noted, this is an area of extreme variability between DBMS (and Informix is certainly one of the variant ones).

Upvotes: 0

ysth
ysth

Reputation: 98388

In MySQL, this would be

where time(datetimefield) > '17:00:00'

Upvotes: 4

David Aldridge
David Aldridge

Reputation: 52336

Another Oracle method for simple situations:

select ...
from   ...
where  EXTRACT(HOUR FROM my_date) >= 17
/

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions050.htm#SQLRF00639

Tricky for some questions though, like all records with the time between 15:03:21 and 15:25:45. I'd also use the TO_CHAR method there.

Upvotes: 0

Thilo
Thilo

Reputation: 262474

What database system are you using? Date/time functions vary widely.

For Oracle, you could say

SELECT * FROM TABLE 
  WHERE TO_CHAR(THE_DATE, 'HH24:MI:SS') BETWEEN '17:00:00' AND '23:59:59';

Also, you probably need to roll-over into the next day and also select times between midnight and, say, 6am.

Upvotes: 8

Marc Gravell
Marc Gravell

Reputation: 1062600

The best thing I can think would be: don't use a DateTime field; well, you could use a lot of DATEADD/DATEPART etc, but it will be slow if you have a lot of data, as it can't really use an index here. Your DB may offer a suitable type natively - such as the TIME type in SQL Server 2008 - but you could just as easily store the time offset in minutes (for example).

Upvotes: 1

Related Questions