Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««89101112»»»

Comparison of Dates in SQL Expand / Collapse
Author
Message
Posted Thursday, April 30, 2009 2:48 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Saturday, August 30, 2014 4:20 PM
Points: 11,194, Visits: 11,142
Amber.Brouillard (4/30/2009)
Nevermind, as I look at it closer, I see your point. Thanks for the correction.

Saints be praised




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #708069
Posted Thursday, April 30, 2009 2:53 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Saturday, August 30, 2014 4:20 PM
Points: 11,194, Visits: 11,142
TheSQLGuru (4/30/2009)
...there is also the same issue of index scan/seek if you are using a function on a column in a where clause.

Unless you use the solution posted by Lynn and others:

where
referencedate >= dateadd(dd, datediff(dd, 0, @comparedate), 0) and -- Beginning of this day
referencedate < dateadd(dd, datediff(dd, 0, @comparedate) + 1, 0) -- Beginning of next day

AFAIK dateadd and datediff are functions

Generally it is true though!

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #708073
Posted Thursday, April 30, 2009 3:52 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:23 AM
Points: 4,352, Visits: 6,168
Paul White (4/30/2009)
TheSQLGuru (4/30/2009)
...there is also the same issue of index scan/seek if you are using a function on a column in a where clause.

Unless you use the solution posted by Lynn and others:

where
referencedate >= dateadd(dd, datediff(dd, 0, @comparedate), 0) and -- Beginning of this day
referencedate < dateadd(dd, datediff(dd, 0, @comparedate) + 1, 0) -- Beginning of next day

AFAIK dateadd and datediff are functions

Generally it is true though!

Paul


Reread my post Paul - I said using a function on a COLUMN. Your example is using a function on a VARIABLE. Just a slight difference there.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #708130
Posted Thursday, April 30, 2009 3:58 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Saturday, August 30, 2014 4:20 PM
Points: 11,194, Visits: 11,142
TheSQLGuru (4/30/2009)
Reread my post Paul - I said using a function on a COLUMN. Your example is using a function on a VARIABLE. Just a slight difference there.

Ah. Yes. Well.

A million apologies, deduct 100 smart-*** points from me!

Maybe the next version of SQL Server will be able to constant-fold entire columns

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #708137
Posted Friday, May 1, 2009 6:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 1, 2011 8:28 AM
Points: 2, Visits: 20
I have gone through the entore thread. Could you please tell me how my solution is so suboptimal. Will it not work in any condition or will it be having a bad impact on performance. Just for better of my understanding...

Thanks,
Ashish
Post #708375
Posted Friday, May 1, 2009 7:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Saturday, August 30, 2014 4:20 PM
Points: 11,194, Visits: 11,142
Ashish Pathak (5/1/2009)
I have gone through the entore thread. Could you please tell me how my solution is so suboptimal. Will it not work in any condition or will it be having a bad impact on performance. Just for better of my understanding...

Did you miss the entire discussion around using functions on columns and the effect that has on the ability of the optimizer to seek on an index rather than scan?

How?







Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #708399
Posted Friday, May 1, 2009 7:40 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:23 AM
Points: 4,352, Visits: 6,168
Ashish Pathak (5/1/2009)
I have gone through the entore thread. Could you please tell me how my solution is so suboptimal. Will it not work in any condition or will it be having a bad impact on performance. Just for better of my understanding...

Thanks,
Ashish


Like Paul said, the problem is that using a function on a column almost always prevents the use of an index seek (if an index is useable) and the engine must resort to an index scan (i.e. reading ALL of the index to find values, as opposed to very efficiently seeking down the btree to get the required rows). Another, sometimes incredibly damaging, issue is that statistics aren't usable either - which can lead to horribly inefficient query plans overall.

I will say that in many, if not most, cases this issue isn't even noticed. There may not even be an index available, in which case all queries would have to use a table scan. Or the where clause would hit such a large portion of the rows that an index scan is more efficient. Or there simply aren't many rows in the table in which case an index seek might take 3 reads whereas a scan would take 100 - simply not a noticeable difference in most cases. But if you have 100M rows an index seek might take 5 or 6 reads but the scan could take tens of thousands - and THAT will leave a mark. :)

Having said all that, there is still NO EXCUSE for writing suboptimal code like this once you know how to do it correctly. Yes, sometimes you have to be expedient in your coding to hit a deadline and taking shortcuts can allow you to write code faster. But this isn't one of those situations. It takes marginally more (if any) effort to do it correctly in this case.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #708435
Posted Friday, May 1, 2009 8:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 28, 2014 6:36 AM
Points: 19, Visits: 86
Using the DATEDIFF function works but can be very slow.

I prefer using:

DECLARE
@FromDate DATETIME,
@ThruDate DATETIME

SET @FromDate = CONVERT(VARCHAR, GETDATE(), 101) --mm/dd/yyyy 00:00:00.000
SET @ThruDate = DATEADD(DAY, 1, @FromDate)
SET @ThruDate = DATEADD(MS, -3, @ThruDate) --mm/dd/yyyy 23:59:59.997

SELECT * FROM Employee WHERE HireDate BETWEEN @FromDate AND @ThruDate

Using -3 millliseconds will give you the maximum time Sql Server can use.
Post #708498
Posted Friday, May 1, 2009 9:34 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:34 PM
Points: 23,343, Visits: 32,079
Ed (5/1/2009)
Using the DATEDIFF function works but can be very slow.

I prefer using:

DECLARE
@FromDate DATETIME,
@ThruDate DATETIME

SET @FromDate = CONVERT(VARCHAR, GETDATE(), 101) --mm/dd/yyyy 00:00:00.000
SET @ThruDate = DATEADD(DAY, 1, @FromDate)
SET @ThruDate = DATEADD(MS, -3, @ThruDate) --mm/dd/yyyy 23:59:59.997

SELECT * FROM Employee WHERE HireDate BETWEEN @FromDate AND @ThruDate

Using -3 millliseconds will give you the maximum time Sql Server can use.


I'd prefer this:

DECLARE
@FromDate DATETIME,
@ThruDate DATETIME

SET @FromDate = dateadd(dd, datediff(dd, 0, getdate()), 0) --mm/dd/yyyy 00:00:00.000
SET @ThruDate = dateadd(dd, 1, @FromDate)

SELECT * FROM Employee WHERE HireDate >= @FromDate AND HireDate < @ThruDate;




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #708534
Posted Friday, May 1, 2009 9:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
Ed (5/1/2009)
Using -3 millliseconds will give you the maximum time Sql Server can use.


True with datetime, but no longer true on SQL 2008 if you start messing with the higher precision datetime data types. I've seen someone carry that 'convention' over to DATETIME2 and then wonder why they're missing rows.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #708544
« Prev Topic | Next Topic »

Add to briefcase «««89101112»»»

Permissions Expand / Collapse