T-SQL Tuesday #1: Date/Time Tricks

SQL Server, T-SQL
I'm going to try out Adam Machanic's idea for a blog party. The topic this month are Date/Time tricks. Instead of supplying a trick for Date/Time, I'm going to caution you about the tricks that you use. Let's take a simple issue. You want to pull back data from a table, let's use the Production.TransactionHistoryArchive in AdventureWorks2008, for a given month of data. Before we run the query, let's create an index on the table: CREATE INDEX ixTest ON Production.TransactionHistoryArchive (TransactionDate) The query itself is pretty simple. This is one mechanism that will retrieve the data for the month of July in 2003: SELECT tha.TransactionID FROM Production.TransactionHistoryArchive AS tha WHERE DATEPART(yy,tha.TransactionDate) = 2003 AND DATEPART(mm,tha.TransactionDate) = 7 In theory you should be able to use the index that was created earlier,…
Read More