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.

A connection was successfully established with the server, but then an error occurred during the pre-login handshake.

Today when I was connecting my SQL server from some client system, connection established successfully with the server. However, then an error occurred during the pre-login handshake. Following is the detailed error i am receiving. 

(provider: SSL Provider, error: 0 - The wait operation timed out.)
A connection was successfully established with the server, but then an error occurred during the pre-login handshake.

Then I check that TCP/IP protocol is not enabled for clients. Without TCP/IP protocol you can not access the sql server from other clients. Following is the method to enable the protocol.

Go to the Start-->Programs-->SQL Server--> Configuration Tools-->SQL Configuration Manager.

Expand the SQL Network Configuration and click on the PROTOCOLS node

Right click on TCP/IP and open up the PROPERTIES panel

Select the IP ADDRESS tab

Make sure the values for the IP ADDRESS fields are correct and match the system it is running on. If its not the same then provide the valid ip through which you want to access sql server from client machine.

Restart the service, and I I can connect to SQL Server from client.