CODEWITHSUNDEEP
sqldatetime
Paola Barrios Taibel
Paola Barrios Taibel

Reputation: 33

How can I write a WHERE clause in SQL to filter a DATETIME column by the time of day (overnight)?

I'm trying to filter time date from a DateTime table to create two separate tables:

I've been able to filter the day-shift using this query:

SELECT
  tbLogTimeValues.DateTimeStamp
  ,tbLogTimeValues.SeqNo
  ,tbLogTimeValues.FloatVALUE
  ,tbLogTimeValues.ParentID
  ,tbLogTimeValues.OdometerValue
FROM tbLogTimeValues
WHERE datepart("hh", tbLogTimeValues.DateTimeStamp) >= 07
AND datepart("hh",   tbLogTimeValues.DateTimeStamp) <= 19

but I don't know how to filter for the night-shift.

Upvotes: 2

Views: 186

Answers (2)

Bohemian
Bohemian

Reputation: 425458

Use NOT BETWEEN:

WHERE datepart("hh", tbLogTimeValues.DateTimeStamp) NOT BETWEEN 7 AND 19

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Just change the where clause to its inverse:

WHERE datepart(hour, tbLogTimeValues.DateTimeStamp) < 07 or
      datepart(hour, tbLogTimeValues.DateTimeStamp) > 19

Upvotes: 0

Related Questions