SQL Server Express 2014：如何查询同一天内起止的活动数据

Hey there! Let's work through this query issue together—no worries, we've all stumbled through SQL syntax and logic when starting out.

First, let's break down the problems in your original statement:

1. Syntax Error #

Your SELECT clause has an extra comma after Stoptime right before FROM—SQL Server will throw an error because of that. Always double-check for trailing commas in column lists!

2. Logical & Typo Issues #

• You misspelled INT as INTERGER (small typo, but it breaks the query!)
• More importantly, subtracting two datetime values in SQL Server returns a floating-point number representing days, not hours or minutes. So CAST(Stoptime - Starttime AS INT) = 1 would look for engagements that last exactly 1 full day—not ones that start and end on the same day.

Correct Queries Based on Your Table Structure #

Let's cover the common scenarios for your table columns:

Scenario 1: StartTime and Stoptime are datetime types (include date + time)

If these columns store full date-time values, we just need to check if their date parts match:

SELECT EngagementNumber, StartDate, StartTime, Stoptime
FROM Engagements
WHERE CAST(StartTime AS DATE) = CAST(Stoptime AS DATE)

CAST(AS DATE) strips off the time portion, so we can compare just the dates directly.

Scenario 2: StartDate is a DATE type, StartTime is TIME, and Stoptime is datetime

Here, we compare the explicit StartDate to the date part of Stoptime:

SELECT EngagementNumber, StartDate, StartTime, Stoptime
FROM Engagements
WHERE StartDate = CAST(Stoptime AS DATE)

Scenario 3: StartDate is DATE, StartTime and Stoptime are both TIME types

If your end time is just a time value (no date), we need to ensure the end time doesn't roll over to the next day. So we check that Stoptime is not earlier than StartTime:

SELECT EngagementNumber, StartDate, StartTime, Stoptime
FROM Engagements
WHERE StartTime <= Stoptime

Any of these should get you the same-day engagements you're looking for. Test them out and adjust based on your actual column data types!

内容的提问来源于stack exchange，提问作者Timmy Nguyen