April 13, 2017 at 9:43 pm
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,
EndDatedatetime
)
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
April 14, 2017 at 1:12 am
rocky_498 - Thursday, April 13, 2017 9:43 PMI 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.000am I right? Please correct me. Thank You in Advance.
These times are all using the 24-hour clock, so 01:15 does not fall between 09:00 and 17:00.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply