SQL Datetime Convertion

You often required to convert string to sql datetime.

I found the following list in SQLUSA best practice site. Just crossposting this for reference.

— SQL Server string to date / datetime conversion – datetime string format sql server

— MSSQL string to datetime conversion – convert char to date – convert varchar to date

— Subtract 100 from style number (format) for yy instead yyyy (or ccyy with century)

SELECT convert(datetime, ‘Oct 23 2012 11:01AM’, 100) — mon dd yyyy hh:mmAM (or PM)

SELECT convert(datetime, ‘Oct 23 2012 11:01AM’) — 2012-10-23 11:01:00.000

— Without century (yy) string date conversion – convert string to datetime function

SELECT convert(datetime, ‘Oct 23 12 11:01AM’, 0) — mon dd yy hh:mmAM (or PM)

SELECT convert(datetime, ‘Oct 23 12 11:01AM’) — 2012-10-23 11:01:00.000

Drop database Error: ALTER DATABASE failed because a lock could not be placed on database

I was trying to drop a database from my development environment. But I was getting the following error.

Error:

Alter failed for Database ‘AdventureWorks’. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1447.4+((KJ_RTM).100213-0103+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

——————————
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

ALTER DATABASE failed because a lock could not be placed on database ‘AdventureWorks’. Try again later.
ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 5061)

 

Resolution: 

This means there are some process already locked the database and you cannot drop it before that process release the lock.

To see which process is locking the database. Run the following  command

exec sp_who2

This will list all processes and uid that has a lock on databases.

In my case, I can see PID 52 was holding the AdventureWorks database. As it is my Dev environment and i know that nothing will harm any other system if I kill this process and eventually drop the database, I issued the following command.

kill <PID> 

KILL 52 //In my case 

After you have kill all the processes that are holding a lock in your database, you will be able to drop the database.