Converting a date within the WHERE or JOIN areas

Question

Converting a date within the WHERE or JOIN areas

BrainDonor

SSCoach

Points: 19254
More actions
June 9, 2009 at 2:57 am

#205997

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
Steve Hall
Linkedin
Blog Site

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Allister Reid SSCrazy Points: 2666 More actions · Answer 1

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)

BrainDonor SSCoach Points: 19254 More actions · Answer 2

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.

Steve Hall
Linkedin
Blog Site

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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, MVP, M.Sc (Comp Sci)
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

BrainDonor SSCoach Points: 19254 More actions · Answer 4

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

Steve Hall
Linkedin
Blog Site

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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, MVP, M.Sc (Comp Sci)
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

BrainDonor SSCoach Points: 19254 More actions · Answer 6

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.

Steve Hall
Linkedin
Blog Site

Allister Reid SSCrazy Points: 2666 More actions · Answer 7

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

There is. Calculated persisted columns can be indexed. Full details in Books Online.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

amenjonathan SSCrazy Points: 2482 More actions · Answer 9

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