Help with SQL Query "Time Range" with Between function

• rocky_498

  SSCertifiable

  Points: 6532

  More actions

  April 13, 2017 at 9:43 pm

  #401944

  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

• drew.allen

  SSC Guru

  Points: 77013

  More actions

  April 14, 2017 at 1:12 am

  #1938145

  rocky_498 - Thursday, April 13, 2017 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.

  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