Between Function not working as suppose to in SQL -Help

Transact-SQL
1

Here is the sample code...

CREATE TABLE #Time1
(
id INT,
City VARCHAR(10),
Atime_Source DATETIME

)

INSERT INTO #Time1
( id,City, Atime_Source )
SELECT 1,'Chicago','2017-04-10 04:28:10.000'

CREATE TABLE #Time2
(
ID INT,
City VARCHAR(20),
StartDate DATETIME,
EndDate datetime

)

INSERT INTO #Time2
( ID,City, StartDate, EndDate )
SELECT '4','Chicago','1900-01-01 04:00:00.000','1900-01-01 06:00:00.000'

SELECT * FROM #Time1
SELECT * FROM #Time2

SELECT
T1.id
,T2.ID
,T1.Atime_Source
,T2.StartDate
,T2.EndDate
FROM #Time1 T1
INNER JOIN #Time2 T2 ON T2.City = T1.City
AND DATEADD(minute, DATEDIFF(minute, DATEDIFF(day, 0, T1.Atime_Source), T1.Atime_Source), 0) BETWEEN T2.StartDate AND T2.EndDate

For some reason in original data (Can't paste here, to big" sometimes working and sometimes don't when I used the time to check in BETWEEN function.

Please any advise would be great appreciate.

Thank You.

2

I would replace this:

with this:

and cast(cast(T1.Atime_Source as time) as datetime)>=T2.StartDate
and cast(cast(T1.Atime_Source as time) as datetime)<T2.EndDate
3

Here is a good example. I just create a sample data and Please educate me if I am wrong....

My understanding is
1900-01-01 01:15:00.000 is between 1900-01-01 09:00:00.000 and 1900-01-01 17:00:00.000

am I right? Please correct me. Thank You in Advance.

Here is the code.

CREATE TABLE #Time11
(
id INT,
City VARCHAR(10),
Atime_Source DATETIME

)

INSERT INTO #Time11
( id,City, Atime_Source )
SELECT 1,'Chicago','1900-01-01 01:15:00.000'
UNION
SELECT 1,'New York','1900-01-01 01:15:00.000'

CREATE TABLE #Time21
(
ID INT,
City VARCHAR(20),
StartDate DATETIME,
EndDate datetime

)

INSERT INTO #Time21
( ID,City, StartDate, EndDate )
SELECT '4','Chicago','1900-01-01 09:00:00.000','1900-01-01 17:00:00.000'
UNION
SELECT '5','Chicago','1900-01-01 18:00:00.000','1900-01-01 09:00:00.000'

SELECT * FROM #Time11
SELECT * FROM #Time21

SELECT
T1.id
,T2.ID
,T1.Atime_Source
,T2.StartDate
,T2.EndDate
FROM #Time11 T1
INNER JOIN #Time21 T2 ON T2.City = T1.City
AND DATEADD(minute, DATEDIFF(minute, DATEDIFF(day, 0, T1.Atime_Source), T1.Atime_Source), 0) BETWEEN T2.StartDate AND T2.EndDate

--OR

SELECT
T1.id
,T2.ID
,T1.Atime_Source
,T2.StartDate
,T2.EndDate
FROM #Time11 T1
INNER JOIN #Time21 T2 ON T2.City = T1.City
and cast(cast(T1.Atime_Source as time) as datetime)>=T2.StartDate
and cast(cast(T1.Atime_Source as time) as datetime)<T2.EndDate

4

No

01:15 because is not between 09:00 and 17:00

One important point to note about BETWEEN is that it matches the end point, so it WOULD match 17:00 but not 17:00:00.001

Most people want "up to" the end point, so it is more common to do GREATER THAN StartPoint AND LESS THAN EndPoint_Plus_One_Unit

This will show you a test for your value, and also test for Start/End values

SELECT	CASE WHEN CONVERT(Datetime, MyDateTime)
			BETWEEN CONVERT(Datetime, '19000101 09:00:00.000')
			AND CONVERT(Datetime, '19000101 17:00:00.000')
		THEN 'BETWEEN'
		ELSE 'outside'
		END,
	CASE WHEN CONVERT(Datetime, MyDateTime) >= CONVERT(Datetime, '19000101 09:00:00.000')
			AND CONVERT(Datetime, MyDateTime) < CONVERT(Datetime, '19000101 17:00:00.000')
		THEN 'IN RANGE'
		ELSE 'outside'
		END,
		MyDateTime
FROM
(
	SELECT [MyDateTime] = '19000101 01:15:00.000'
	UNION ALL SELECT '19000101 09:00:00.000'
	UNION ALL SELECT '19000101 17:00:00.000'
) AS X