[SQL] 日期區間執行時間

在案子當中，
客戶的某個需求就是動態前六個月的資料，
但前六個月不包含當月，
也就是要減一個月在往前推六個月，
接著在撰寫SQL語法時，
我一開始的寫法如下:

substring(CONVERT(char(8),DATEADD(month, 0, CONVERT(datetime, CAST(V_Sales_Invoice_Body.IssuedDate AS varchar(8)))) ,112),1,6)
 >= substring(CONVERT(char(8),DATEADD(month, -6, GETDATE()),112 ),1,6)
 AND
substring(CONVERT(char(8),DATEADD(month, 0, CONVERT(datetime, CAST(V_Sales_Invoice_Body.IssuedDate AS varchar(8)))) ,112),1,6)
 <= substring(CONVERT(char(8),DATEADD(month, -1, GETDATE()),112 ),1,6)

因該日期欄位原本為numeric，
所以必須先轉成文字，再轉成日期，
看似做了很多轉換並用AND串成兩個條件，
但執行時間卻比以下語法還要快。

CONVERT(datetime, CAST(ProcessMonth * 100 + 1 AS varchar(8)),112)
BETWEEN DATEADD(month, - 7, GETDATE()) AND DATEADD(month, - 1,GETDATE())

此語法是利用另一個欄位ProcessMonth (ex. 201308)做運算再做轉換，
並用Between 串連。

SharePoint 2013 安裝後序號取得

在安裝完SP2013 後，
假使忘記當初的產品序號，
該如何得知呢?
請執行以下程式

using System;
using System.Collections;
using Microsoft.Win32;


namespace GetInstalledSharePointSku
{
    class Program
    {
        internal static Hashtable _products;

        public static Hashtable SharePointProducts
        {
            get 
            {
                if (_products == null)
                {
                    _products = new Hashtable();

                    _products.Add("35466B1A-B17B-4DFB-A703-F74E2A1F5F5E", "Project Server 2013");
                    _products.Add("BC7BAF08-4D97-462C-8411-341052402E71", " Project Server 2013 Preview");

                    _products.Add("C5D855EE-F32B-4A1C-97A8-F0A28CE02F9C", "SharePoint Server 2013");
                    _products.Add("CBF97833-C73A-4BAF-9ED3-D47B3CFF51BE", "SharePoint Server 2013 Preview");
                    _products.Add("B7D84C2B-0754-49E4-B7BE-7EE321DCE0A9", "SharePoint Server 2013 Enterprise");
                    _products.Add("298A586A-E3C1-42F0-AFE0-4BCFDC2E7CD0", "SharePoint Server 2013 Enterprise Preview");

                    _products.Add("D6B57A0D-AE69-4A3E-B031-1F993EE52EDC ", "Microsoft Office Web Apps Server 2013");
                    _products.Add("9FF54EBC-8C12-47D7-854F-3865D4BE8118", "SharePoint Foundation 2013");
                }
                
                return _products;
            }
        }

        private const String SharePointProductsRegistryPath = @"SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\15.0\WSS\InstalledProducts\";

        static void Main(string[] args)
        {
            try
            {
                //Open the registry key in read-only mode.
                using (RegistryKey key = Registry.LocalMachine.OpenSubKey(SharePointProductsRegistryPath, false))
                {
                    //Get all of the installed product code/SKUId pairs.
                    foreach (String value in key.GetValueNames())
                    {
                        try
                        {
                            //Get the SKUId and see whether it is a known product.
                            String SKUId = key.GetValue(value) as String;

                            if (SharePointProducts[SKUId] != null)
                            {
                                Console.WriteLine("Product Installed: {0}", SharePointProducts[SKUId]);
                            }
                            else
                            {
                                Console.WriteLine("Unknown Product: {0}", SKUId);
                            }
                        }
                        catch (Exception e)
                        {
                            Console.WriteLine("Could not read key exception was {0}", e.Message);
                        }
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Could not open key exception was {0}", e.Message);
            }
            Console.Read();
        }
    }
}