tag:blogger.com,1999:blog-7998687752365941655.post1451637891072767218..comments2022-03-25T16:46:26.176+05:30Comments on VM Blogs: Different ways to implement date range comparison logic in Transact SQL Visakh Murukesanhttp://www.blogger.com/profile/02874515877085669105noreply@blogger.comBlogger7125tag:blogger.com,1999:blog-7998687752365941655.post-73527491042583418172013-01-09T22:41:37.537+05:302013-01-09T22:41:37.537+05:30If there are lots of date range filters based on t...If there are lots of date range filters based on these values, then it might prove a little better to set values in variables on top rather than repeating it everywhere.<br />Similarly use of variables to hold calculated values from parameters will also prove to be beneficial in cases where parameter sniffing happens as use of variables will not cause bad plan to be cached and reusedVisakh Murukesanhttps://www.blogger.com/profile/02874515877085669105noreply@blogger.comtag:blogger.com,1999:blog-7998687752365941655.post-10402527010202420492013-01-09T22:30:27.554+05:302013-01-09T22:30:27.554+05:30Regarding records created at midnight, ok fair eno...Regarding records created at midnight, ok fair enough, I couldn't really care less but my main point was that I have always thought it better to set the date variables at the top of the query so they only need to be calculated once, rather than in the WHERE clause where they need to be calculated once for each and every row in the dataset.<br />I'd appreciate your comments on this as, like I said previously, its something we all have to deal with. Thanks.Nickhttps://www.blogger.com/profile/08696629638976495596noreply@blogger.comtag:blogger.com,1999:blog-7998687752365941655.post-73506908563960023922013-01-09T17:01:51.155+05:302013-01-09T17:01:51.155+05:30Nope. that's not true. Try my illustration abo...Nope. that's not true. Try my illustration above to see the difference. it will not include records created at midnight next day as I'm using >= and <.Visakh Murukesanhttps://www.blogger.com/profile/02874515877085669105noreply@blogger.comtag:blogger.com,1999:blog-7998687752365941655.post-29246454505729838072013-01-09T15:05:23.376+05:302013-01-09T15:05:23.376+05:30Thanks for your reply. Surely though, the issue of...Thanks for your reply. Surely though, the issue of records created at midnight applies to all the solutions that use DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1).<br />In any case, my point was that I have always thought it better to set the date variables at the top of the query so they only need to be calculated once, rather than in the WHERE clause where they need to be calculated for every row in the dataset.<br />Nickhttps://www.blogger.com/profile/08696629638976495596noreply@blogger.comtag:blogger.com,1999:blog-7998687752365941655.post-71190642228022536902013-01-08T22:02:26.433+05:302013-01-08T22:02:26.433+05:30See the illustration below to understand the diffe...See the illustration below to understand the difference<br /><br />declare @dteStart datetime, @dteEnd datetime<br />set @dteStart = DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)<br />set @dteEnd = DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)<br />declare @t table<br />(<br />id int identity(1,1),<br />dateval datetime<br />)<br /><br />insert @t (dateval)<br />select GETDATE() union all<br />select GETDATE()-0.4 union all<br />select dateadd(dd,datediff(dd,0,GETDATE()),1)<br /><br />SELECT *<br />FROM @t<br />WHERE dateval between @dteStart and @dteEnd<br /><br /><br /><br />SELECT *<br />FROM @t<br />WHERE dateval >= @dteStart and dateval < @dteEnd<br /><br />output<br />-----------------------<br />id dateval<br />-----------------------<br />1 2013-01-08 21:59:28.403<br />2 2013-01-08 12:23:28.403<br />3 2013-01-09 00:00:00.000<br /><br />id dateval<br />-----------------------<br />1 2013-01-08 21:59:28.403<br />2 2013-01-08 12:23:28.403Visakh Murukesanhttps://www.blogger.com/profile/02874515877085669105noreply@blogger.comtag:blogger.com,1999:blog-7998687752365941655.post-45727270327833288292013-01-08T15:39:43.720+05:302013-01-08T15:39:43.720+05:30This is analogous to my method 5 but it has a slig...This is analogous to my method 5 but it has a slight problem as it will cause any records created at midnight of DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1) also to be included which is not what we want. This is because BETWEEN translates to >= AND <= whereas what we want is to exclude the last = to avoid the records in the end boundary getting included.Visakh Murukesanhttps://www.blogger.com/profile/02874515877085669105noreply@blogger.comtag:blogger.com,1999:blog-7998687752365941655.post-22320586098261985592013-01-08T14:42:04.114+05:302013-01-08T14:42:04.114+05:30That's an interesting post as its something th...That's an interesting post as its something that I think we all encounter. I always prefer the following approach as it doesn't involve the use of any functions in the WHERE clause. DOn't know how it compares performance-wise though.<br />What do you think?<br /><br />declare @dteStart datetime, @dteEnd datetime<br />set @dteStart = DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)<br />set @dteEnd = DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)<br /><br />SELECT *<br />FROM Table<br />WHERE dateField between @dteStart and @dteEndNickhttps://www.blogger.com/profile/08696629638976495596noreply@blogger.com