Date Time Conversion in T-SQL

select convert(varchar, getdate(), 1) 03/10/09
select convert(varchar, getdate(), 3) 10/03/09
select convert(varchar, getdate(), 5) 10-03-09
select convert(varchar, getdate(), 6) 10 Mar 09
select convert(varchar, getdate(), 10) 03-10-09
select convert(varchar, getdate(), 11) 09/03/10
select convert(varchar, getdate(), 101) 03/10/2009
select convert(varchar, getdate(), 103) 10/03/2009
select convert(varchar, getdate(), 107) Mar 10, 2009
select convert(varchar, getdate(), 110) 03-10-2009
select convert(varchar, getdate(), 111) 2009/03/10

To drop the timestamp from the date and then replace timestamp with zeros use..
select GETDATE() -- 2009-03-10 15:31:56.340
select cast(convert (varchar(10), getdate(), 101) as datetime)TimeDate -- 2009-03-10 00:00:00.000

Attach DB and Shrink Log T-SQL Scripts

To attach a database with only a mdf file. (This will create new log file)
USE [master]
GO
CREATE DATABASE [Test] ON
( FILENAME = N'D:\Database\2008 Database\Test.mdf' )
FOR ATTACH
GO

To attach a database with mdf and ldf files.
USE [master]
GO
CREATE DATABASE [HandWashDW] ON
( FILENAME = N'D:\Database\2008 Database\HandWashDW.mdf' ),
( FILENAME = N'D:\Database\2008 Database\HandWashDW_log.ldf' )
FOR ATTACH
GO

To shrink the log file in SQL Server 2005
USE myDatabase
GO
DBCC SHRINKFILE(myDatabase_log, 1)
BACKUP LOG myDatabase WITH TRUNCATE_ONLY
DBCC SHRINKFILE(myDatabase_log, 1)


To shrink the logs in 2008, use the following
USE HandWashDW
GO
-- Declare variables
DECLARE @SqlStatement as nvarchar(max)
DECLARE @LogFileLogicalName as sysname

-- Alter the database to simple recovery
SET @SqlStatement = 'ALTER DATABASE ' + DB_NAME() + ' SET RECOVERY SIMPLE'
EXEC ( @SqlStatement )

-- Make sure it has been altered
SELECT [name], [recovery_model_desc] FROM sys.databases WHERE [name] = DB_NAME()

-- Set the log file name variable
SELECT @LogFileLogicalName = [Name] FROM sys.database_files WHERE type = 1

-- Shrink the logfile
DBCC Shrinkfile(@LogFileLogicalName, 1)

-- Alter the database back to FULL
SET @SqlStatement = 'ALTER DATABASE ' + DB_NAME() + ' SET RECOVERY FULL'
EXEC ( @SqlStatement )

-- Make sure it has been changed back to full
SET @SqlStatement = 'SELECT [name], [recovery_model_desc] FROM ' + DB_NAME() + '.sys.databases WHERE [name] = ''' + DB_NAME() + ''''
EXEC ( @SqlStatement )