CODEWITHSUNDEEP
sqlsql-server
bibah
bibah

Reputation: 41

SQL query regarding date and time

I am trying to answer the following question:

Show all engagements in October 2007 that start between noon and 5 P.M

I have tried the following query:

SELECT EngagementNumber, StartDate, StartTime
FROM Engagements
WHERE StartDate <= CAST('2007-10-31' As DATE)
AND EndDate >= CAST('2007-10-01' AS DATE)
AND StartTime Between CAST('12:00:00' AS TIME) AND CAST('17:00:00' AS TIME)

However, the following error is occurring:

Msg 402, Level 16, State 1, Line 1 The data types datetime and time are incompatible in the less than or equal to operator.

I am running this on a SQL Server Database 2008R2 version and wondered if anyone could tell me why this is happening please?

Thanks

Upvotes: 0

Views: 213

Answers (3)

SpringLearner
SpringLearner

Reputation: 13844

try this way

SELECT  EngagementNumber, StartDate, StartTime 
FROM    Engagements
WHERE   StartDate <= '2007-10-31T12:00:00.000' 
  AND   EndDate >=  '2007-10-01T17:00:00.000'

this will work if startdate and enddate are of datetime datatype

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I would just use datepart(hour):

SELECT EngagementNumber, StartDate, StartTime
FROM Engagements
WHERE StartDate <= CAST('2007-10-31' As DATE) AND
      EndDate >= CAST('2007-10-01' AS DATE) and
      datepart(hour, StartTime) >= 12 and datepart(hour, EndTime) < 17;

Upvotes: 0

zkanoca
zkanoca

Reputation: 9918

SELECT 
       EngagementNumber, 
       StartDate, 
       StartTime
FROM 
       Engagements
WHERE
      StartDate <= '2007-10-31' 
      AND EndDate >= '2007-10-01' 
      AND convert(char(8), StartTime , 108) BETWEEN '12:00:00' AND '17:00:00'

108 constant outputs as hh:mm:ss

Upvotes: 2

Related Questions