Converting a date within the WHERE or JOIN areas

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Converting a date within the WHERE or JOIN...

Converting a date within the WHERE or JOIN areas

Rate Topic

Display Mode

Topic Options

Author

Message

BrainDonor

Posted Tuesday, June 09, 2009 2:57 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 4:57 AM
Points: 1,400, Visits: 6,891

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

Post #731245

Allister Reid

Posted Tuesday, June 09, 2009 3:07 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 01, 2013 3:19 AM
Points: 192, Visits: 3,071

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

BrainDonor

Posted Tuesday, June 09, 2009 3:12 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 4:57 AM
Points: 1,400, Visits: 6,891

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

Post #731251

GilaMonster

Posted Tuesday, June 09, 2009 3:32 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 37,687, Visits: 29,946

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

BrainDonor

Posted Tuesday, June 09, 2009 3:44 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 4:57 AM
Points: 1,400, Visits: 6,891

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

Post #731272

GilaMonster

Posted Tuesday, June 09, 2009 3:54 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 37,687, Visits: 29,946

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

BrainDonor

Posted Tuesday, June 09, 2009 4:09 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 4:57 AM
Points: 1,400, Visits: 6,891

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

Post #731287

Allister Reid

Posted Tuesday, June 09, 2009 4:19 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 01, 2013 3:19 AM
Points: 192, Visits: 3,071

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

GilaMonster

Posted Tuesday, June 09, 2009 5:14 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 37,687, Visits: 29,946

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

amenjonathan

Posted Thursday, May 13, 2010 9:36 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 20, 2012 5:21 PM
Points: 84, Visits: 391

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 »

Permissions