asked
T-SQL Date Exclusion
I have a script that finds stored procs that users have defined, but I need to exclude for dates that obviously represent the date on which the COTS product was installed and any related stored procs were created in the database. For example, say I installed WidgetX on 08/29/2015, there should definitely be entries in the create_date field of .sys.objects that correspond to that date. Those are great, but I am not interested in those, or any like them that would be added by WidgetX during service pack upgrades -- I am interested in random stored procedures that might be added on other dates. So I need something like this, except this one won't work because text comparison on a date field: select * from .sys.objects where create_date NOT IN ('20152908%', ''... etc Any ideas on how this could be done? Ultimately, I want to make it easy to change the script to exclude any sp's added during the exact 5-15 minute range that the software is being installed or upgraded.
In sys.objects, you have create_date as a datetime, and you probably want to compare with a date. To sort that out, you'll have to cast the create_date of sys.objects to a date instead of a datetime, in order to make the IN-comparison. Something like this: use AdventureWorks2014; SELECT * FROM sys.objects WHERE CAST(create_date as date) NOT IN ('2014-02-20','2009-04-13'); Beware of this type of cast, where you cast a column in a table, because it will cause a poor execution plan (since the index on create_date becomes more or less useless - the index is on create_date, not in CAST(create_date as date). But for sys.objects, this might not be such a big problem - it normally doesn't hold hundreds of thousands of rows. In Adventureworks2014, it has some 600 rows. But if you do the same in a big table, you might end up with terrible performance.
Is it simpler to exclude the date/time range for WidgetX? SELECT * FROM sys.objects WHERE create_date NOT BETWEEN '2015-08-29' AND '2015-08-30' -- widgetX AND create_date NOT BETWEEN '2016-01-01' AND '2016-01-02' -- widgetY
18 People are following this question.
