InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 11:50:22 GMT20http://www.sqlservercentral.com/Forums/Topic731245-338-1.aspxIn 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) = DWDateAlthough if performance is an issue, I guess we could do it like this:AND TransactionDate >= Table1.DimTimeDateAND TransactionDate < Table2.DimTimeDate --(date + 1d)Totally removing any functions would require a second join on the time dimension at DATEADD(dd,1,DWDate).Thu, 13 May 2010 09:36:27 GMTamenjonathanhttp://www.sqlservercentral.com/Forums/Topic731245-338-1.aspxThere is. Calculated persisted columns can be indexed. Full details in Books Online.Tue, 09 Jun 2009 05:14:37 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic731245-338-1.aspxGail,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 SeqIs there anything analagous in SQL Server?Tue, 09 Jun 2009 04:19:31 GMTAllister Reidhttp://www.sqlservercentral.com/Forums/Topic731245-338-1.aspxI'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.Tue, 09 Jun 2009 04:09:49 GMTBrainDonorhttp://www.sqlservercentral.com/Forums/Topic731245-338-1.aspx[quote][b]BrainDonor (6/9/2009)[/b][hr]If a selection is required on the date part of a datetime field what would be the best way of doing it?[/quote]BETWEEN's often very useful[quote]Another method I have used is using DATEDIFF to check the number of days difference, but does this suffer the same kind of penalty?[/quote][b]Any[/b] 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.Tue, 09 Jun 2009 03:54:53 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic731245-338-1.aspxThanks 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,BrainDonorTue, 09 Jun 2009 03:44:51 GMTBrainDonorhttp://www.sqlservercentral.com/Forums/Topic731245-338-1.aspxAny 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.Tue, 09 Jun 2009 03:32:46 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic731245-338-1.aspxYes, 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.Tue, 09 Jun 2009 03:12:03 GMTBrainDonorhttp://www.sqlservercentral.com/Forums/Topic731245-338-1.aspxThis 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)Tue, 09 Jun 2009 03:07:23 GMTAllister Reidhttp://www.sqlservercentral.com/Forums/Topic731245-338-1.aspxI 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,BrainDonorTue, 09 Jun 2009 02:57:14 GMTBrainDonor