Date time formatting in SQL Server

Below is a small script that shows you the date output in different format:

It just outputs the current date in the various formats so you can quickly find the one your after


DECLARE @date datetime
DECLARE @i int
SET @date = getdate()
SET @i = 1
WHILE(@i <= 14)
  BEGIN
 PRINT CONVERT(VarChar, @i) + ': ' + CONVERT(VarChar, @date, @i)        
 PRINT CONVERT(VarChar, @i+100) + ': ' + CONVERT(VarChar, @date, @i+100)    
 SET @i = @i + 1
  END 
PRINT CONVERT(VarChar, 20) + ': ' + CONVERT(VarChar, @date, 20)        
PRINT CONVERT(VarChar, 120) + ': ' + CONVERT(VarChar, @date, 120)    
PRINT CONVERT(VarChar, 21) + ': ' + CONVERT(VarChar, @date, 21)        
PRINT CONVERT(VarChar, 121) + ': ' + CONVERT(VarChar, @date, 121)    
PRINT CONVERT(VarChar, 126) + ': ' + CONVERT(VarChar, @date, 126)    
PRINT CONVERT(VarChar, 127) + ': ' + CONVERT(VarChar, @date, 127)    
PRINT CONVERT(VarChar, 130) + ': ' + CONVERT(VarChar, @date, 130)    
PRINT CONVERT(VarChar, 131) + ': ' + CONVERT(VarChar, @date, 131)    

Query to create a Table of dates between date range specified

Use below query to create a table of date for given date range:

DECLARE @FromDt AS DateTime = '2013-09-01', @ToDt AS DateTime = '2013-12-31'

CREATE TABLE #TempDtTable(VchDt VARCHAR(10));
WHILE @FromDt <= @ToDt
BEGIN
INSERT INTO #TempDtTable VALUES (CONVERT(VARCHAR(10), @FromDt , 121))
SET @FromDt = DATEADD(DAY, 1, @FromDt)
END
SELECT * FROM #TempDtTable
DROP TABLE #TempDtTable

Restart Computer Failure While uninstalling SQL Server 2008

You may get the below error while uninstalling SQL server: 

A computer restart is required. You must restart this computer before installing SQL Server.

So, now you restart the system. and try again but you got the same error. Now it seems that there are some bad registry entries: Now you had to to do the following

Leave the Setup Support Rules UI open if you are already in there
Open Regedit (Registry Editor)

Go to the following registry key "HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSession Manager"

Delete the "PendingFileRenameOperations"

Click “Re-Run” in Setup Support Rules launch the uninstall again

So now you will have passed the restart computer check and you can continue and get rid of that pesky old version of SQL Server.

Msg 1934, Level 16, State 1, Line 2

Today I was running below query to rebuild all indexes:

EXEC sp_msforeachtable 'ALTER INDEX ALL ON ? REBUILD'

But it failed and shown below error:

Msg 1934, Level 16, State 1, Line 2
ALTER INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

I searched for this on net and got some below solution:

EXEC sp_msforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD'

It means you need the SET QUOTED_IDENTIFIER ON in the sp_msForEachTable as well, because sp_msForEachTable does not have the right setting.