1
select * from table_Sample
where ID = 9229 
and Datetime between '2018-06-18T18:30:00.00'
AND '2018-06-19T18:29:59.59'

2nd Option:

select * from table_Sample
where ID = 9229 
and Datetime >= '2018-06-18T18:30:00.00'
AND Datetime <= '2018-06-19T18:29:59.59'

Can you please explain there are any difference between two query accordingly performance or result set or whatever else?

cSharma
  • 636
  • 8
  • 21
  • Have you tried both? – Rafalon Jun 21 '18 at 10:58
  • yes and I am getting same result set – cSharma Jun 21 '18 at 10:59
  • You maybe have the answer if you try it on your server – Ilyes Jun 21 '18 at 10:59
  • so no performance difference right? – cSharma Jun 21 '18 at 11:01
  • I prefer `>= dt1 AND <= dt2` because you see directly what it means and that its inclusive whereas`BETWEEN` hides this fact. You can also change it easily to (for example) `>= somedate AND < someother`. [What do BETWEEN and the devil have in common?](https://sqlblog.org/2011/10/19/what-do-between-) – Tim Schmelter Jun 21 '18 at 11:06
  • as per Dan Guzman, he said between keyword will convert into >= and <= internal. So better to no need to give a chance to unnecessary conversion as per my understanding. – cSharma Jun 21 '18 at 11:11

1 Answers1

1

Compare the execution plans. You will find the plans are identical because BETWEEN is translated into same >= AND <= predicate as the second query, yielding the same result and performance.

From a functional perspective, also consider that date range queries for a specific date range usually don't want to consider the time component of a temporal type with greater resolution. It's a good practice to specify an inclusive start and exclusive end date in this case. This will avoid inadvertently missing rows with fractional seconds regardless of the data type precision.

SELECT * 
FROM dbo.table_Sample
WHERE ID = 9229 
    AND Datetime >= '2018-06-18T18:30:00.00'
    AND Datetime < '2018-06-20T00:00:00.00';
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71