Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Converting a date within the WHERE or JOIN areas


Converting a date within the WHERE or JOIN areas

Author
Message
BrainDonor
BrainDonor
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2052 Visits: 11203
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
Allister Reid
Allister Reid
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 3073
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
BrainDonor
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2052 Visits: 11203
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47285 Visits: 44392
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
BrainDonor
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2052 Visits: 11203
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47285 Visits: 44392
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
BrainDonor
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2052 Visits: 11203
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
Allister Reid
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 3073
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?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47285 Visits: 44392
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
amenjonathan
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search