SQL query regarding date and time

Question

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

The error suggests you're trying to compare DATETIME to TIME, I would have a look at the data types in your Engagements table and then re-consider your query. — William
– William, Commented Sep 18, 2013 at 11:35
EngagementNumber StartDate EndDate StartTime StopTime ContractPrice CustomerID AgentID EntertainerID 2 2007-09-01 00:00:00.000 2007-09-05 00:00:00.000 1899-12-30 13:00:00.000 1899-12-30 15:00:00.000 200.00 10006 4 1004 3 2007-09-10 00:00:00.000 2007-09-15 00:00:00.000 1899-12-30 13:00:00.000 1899-12-30 15:00:00.000 590.00 10001 3 1005 4 2007-09-11 00:00:00.000 2007-09-17 00:00:00.000 1899-12-30 20:00:00.000 1899-12-31 00:00:00.000 470.00 10007 3 1004 5 2007-09-11 00:00:00.000 2007-09-14 00:00:00.000 1899-12-30 16:00:00.000 1899-12-30 19:00:00.000 1130.00 10006 5 1003 — bibah
– bibah, Commented Sep 18, 2013 at 11:37

zkanoca · Accepted Answer · 2013-09-18 11:36:55Z

2

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

answered Sep 18, 2013 at 11:36

zkanoca

10k10 gold badges54 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bibah Over a year ago

What does the following syntax in the command actually do? AND convert(char(8), StartTime , 108)

zkanoca Over a year ago

@bibah convert StartTime field as hh:mm:ss in 8 character length.

SpringLearner · Accepted Answer · 2013-09-18 11:43:10Z

1

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

edited Sep 18, 2013 at 11:43

answered Sep 18, 2013 at 11:34

SpringLearner

13.9k20 gold badges82 silver badges117 bronze badges

2 Comments

Onkel Toob Over a year ago

From the question I would read that there are actually different columns for the date and the time information, so the WHERE clause should somehow reflect this. Besides that you're selecting engagements that start after October 31st and end before October 1st. I think you mixed something up with the compare operators.

SpringLearner Over a year ago

@papatoob Thanks for pointing the mistake.well Op has posted the DB datas in comment.It shows date plus time(2007-09-01 00:00:00.000)

Gordon Linoff · Accepted Answer · 2013-09-18 11:40:46Z

0

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;

answered Sep 18, 2013 at 11:40

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

SQL query regarding date and time

3 Answers 3

2 Comments

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Related