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

Converting a date within the WHERE or JOIN areas Expand / Collapse
Author
Message
Posted Tuesday, June 09, 2009 2:57 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:48 AM
Points: 1,533, Visits: 7,916
I regularly use such code as:

CONVERT(VARCHAR(11),date_field,106)
in order to convert a date within a database to a format more suitable for displaying.

But I've come across code that does such things within the WHERE clause and the JOIN clause. For example:
WHERE CAST(CONVERT(VARCHAR(11),Expiry_Date,106) AS DATETIME) >= CAST(CONVERT(VARCHAR(11),GETDATE(),106) AS DATETIME))

I've never bothered converting dates where they haven't been required as output fields for displaying or suchlike.
My first thought is that this code is unnecessary and will just slow things down.
Am I correct in believing this, or is there a good reason for using such code where the date will never be selected for output?

Regards,
BrainDonor


BrainDonor
Linkedin
Blog Site
Post #731245
Posted Tuesday, June 09, 2009 3:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 09, 2014 9:24 AM
Points: 192, Visits: 3,073
This is code for getting a date without the time portion.

I'm not sure about actual performance, I'd need to test, however, the following keeps the date as a dat, without the (IMO) ugly cast.

select dateadd(day, datediff(day, 0, getdate()), 0)
Post #731249
Posted Tuesday, June 09, 2009 3:12 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:48 AM
Points: 1,533, Visits: 7,916
Yes, I hadn't thought about comparing just the date (without the time) - obvious now that someone else points it out! Still an ugly way of doing it though.

BrainDonor
Linkedin
Blog Site
Post #731251
Posted Tuesday, June 09, 2009 3:32 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 @ 7:59 AM
Points: 41,530, Visits: 34,447
Any form of function on a column will prevent index seeks on that column. So if you use that form in a where/join, you may very well be hindering performance.


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 #731260
Posted Tuesday, June 09, 2009 3:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:48 AM
Points: 1,533, Visits: 7,916
Thanks Gail. I suspected that, and thought using a CAST and CONVERT wouldn't be too helpfull either.
If a selection is required on the date part of a datetime field what would be the best way of doing it?
Another method I have used is using DATEDIFF to check the number of days difference, but does this suffer the same kind of penalty?

Thanks,
BrainDonor


BrainDonor
Linkedin
Blog Site
Post #731272
Posted Tuesday, June 09, 2009 3:54 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 @ 7:59 AM
Points: 41,530, Visits: 34,447
BrainDonor (6/9/2009)
If a selection is required on the date part of a datetime field what would be the best way of doing it?

BETWEEN's often very useful

Another method I have used is using DATEDIFF to check the number of days difference, but does this suffer the same kind of penalty?


Any form of function on a column will prevent index seeks on that column. That includes all SQL functions, all forms or arithmetic, string concatenation, etc. If you apply any change to a column in the where/join then it's no longer eligible for index seeks.



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 #731282
Posted Tuesday, June 09, 2009 4:09 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:48 AM
Points: 1,533, Visits: 7,916
I've just looked at the data that the query is looking at and the time part of the datetime fields are always zero, so there's no need to do this work to strip the time out - they should have been able to compare the fields without any messing about.

I'll have a play with slightly healthier ways of comparing dates where there is a time to consider/remove.

Thanks for the assistance Gail, Allister.

BrainDonor.


BrainDonor
Linkedin
Blog Site
Post #731287
Posted Tuesday, June 09, 2009 4:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 09, 2014 9:24 AM
Points: 192, Visits: 3,073
Gail,
Is there any way to add an index to the function of a column? In Foxpro I am able to do things like:

INDEX ON VAL(SeqNum) AS Seq

Is there anything analagous in SQL Server?
Post #731294
Posted Tuesday, June 09, 2009 5:14 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 @ 7:59 AM
Points: 41,530, Visits: 34,447
There is. Calculated persisted columns can be indexed. Full details in Books Online.


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 #731316
Posted Thursday, May 13, 2010 9:36 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, December 16, 2013 10:42 AM
Points: 96, Visits: 434
In 2008, the best way to truncate hours is CONVERT(date, YourDate). Took long enough to get some new time datatypes. hah!

Also, since the data warehouse I work with is using day as the granularity, we often need to use something like this:

AND CONVERT(date, TransactionDate) = DWDate

Although if performance is an issue, I guess we could do it like this:

AND TransactionDate >= Table1.DimTimeDate
AND TransactionDate < Table2.DimTimeDate --(date + 1d)

Totally removing any functions would require a second join on the time dimension at DATEADD(dd,1,DWDate).


-------------------------------------------------------------------------------------------------
My SQL Server Blog
Post #921408
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse