MS SQL Server Utility Snippets

gistfile1.sql

---- Enable Ad hoc Queries ----
sp_configure 'show advanced options', 1
reconfigure
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure


-------------------- DATE RELATED --------------------
---- Replace GETDATE() with your fieldname if needed.

---- First of the month ----
SELECT DATEADD(d,1-DAY(GETDATE()), GETDATE())

---- Truncate time part of a SQL Server datetime field ----
SELECT CAST(FLOOR(CAST(GETDATE() as float)) as DATETIME)

---- Convert 6 digit date to SQL Server DATETIME
---- e.g. 111274 => 2011-10-01, 110031 => 2010-01-31
---- account for day index starting at 1 by starting at 1899-12-31 instead of 1900-01-01
DATEADD(d, my_field%1000, DATEADD(yyyy, FLOOR(my_field/1000), '1899-12-31'))