Date Conversion in SQL

SELECT convert(datetime,'15/03/18',3) -- 2018-03-15 00:00:00.000

SELECT convert(datetime,'15.03.18',4) -- 2018-03-15 00:00:00.000

SELECT convert(varchar,getdate()) -- Mar 15 2018 10:35AM

SELECT convert(varchar,getdate(),0) -- Mar 15 2018 10:35AM

SELECT convert(varchar,getdate(),1) -- 03/15/18

SELECT convert(varchar,getdate(),2) -- 18.03.15

SELECT convert(varchar,getdate(),3) -- 15/03/18

SELECT convert(varchar,getdate(),4) -- 15.03.18

SELECT convert(varchar,getdate(),5) -- 15-03-18

SELECT convert(varchar,getdate(),6) -- 15 Mar 18

SELECT convert(varchar,getdate(),7) -- Mar 15, 18

SELECT convert(varchar,getdate(),8) -- 10:39:39

SELECT convert(varchar,getdate(),9) -- Mar 15 2018 10:39:48:373AM

SELECT convert(varchar,getdate(),10) -- 03-15-18

SELECT convert(varchar,getdate(),11) -- 18/03/15

SELECT convert(varchar,getdate(),15) -- 180315

SELECT convert(varchar,getdate(),13) -- 15 Mar 2018 10:41:07:590

SELECT convert(varchar,getdate(),14) -- 10:41:25:903

SELECT convert(varchar,getdate(),20) -- 2018-03-15 10:43:56

SELECT convert(varchar,getdate(),21) -- 2018-03-15 10:44:04.950

SELECT convert(varchar,getdate(),22) -- 03/15/18 10:44:50 AM

SELECT convert(varchar,getdate(),23) -- 2018-03-15

SELECT convert(varchar,getdate(),24) -- 10:45:45

SELECT convert(varchar,getdate(),25) -- 2018-03-15 10:46:11.263

SELECT convert(varchar, getdate(), 100) -- Oct 23 2016 10:22AM (or PM)
SELECT convert(varchar, getdate(), 101) -- 10/23/2016

SELECT convert(varchar, getdate(), 102) -- 2016.10.23

SELECT convert(varchar, getdate(), 103) -- 23/10/2016

SELECT convert(varchar, getdate(), 104) -- 23.10.2016

SELECT convert(varchar, getdate(), 105) -- 23-10-2016

SELECT convert(varchar, getdate(), 106) -- 23 Oct 2016

SELECT convert(varchar, getdate(), 107) -- Oct 23, 2016

SELECT convert(varchar, getdate(), 108) -- 09:10:34

SELECT convert(varchar, getdate(), 109) -- Oct 23 2016 11:10:33:993AM (or PM)

SELECT convert(varchar, getdate(), 110) -- 10-23-2016

SELECT convert(varchar, getdate(), 111) -- 2016/10/23

SELECT convert(varchar, getdate(), 112) -- 20161023

SELECT convert(varchar, getdate(), 113) -- 23 Oct 2016 06:10:55:383

SELECT convert(varchar, getdate(), 114) -- 06:10:55:383(24h)

SELECT convert(varchar, getdate(), 120) -- 2016-10-23 06:10:55(24h)

SELECT convert(varchar, getdate(), 121) -- 2016-10-23 06:10:55.383

SELECT convert(varchar, getdate(), 126) -- 2016-10-23T06:10:55.383

SELECT [Date] = CAST('20120228' AS date) -- 2012-02-28

SELECT [Datetime] = CAST('20120228' AS datetime) -- 2012-02-28 00:00:00.000

SELECT [Datetime] = CAST('20120228' AS smalldatetime) -- 2012-02-28 00:00:00

DECLARE @sDate varchar(10)

SET @sDate = '2010/03/15'

-- DATEDIFF (delta) between two dates in months

SELECT GETDATE(), DATEDIFF (MONTH, GETDATE(), @sDate)

SELECT GETDATE(), DATEDIFF (MONTH, GETDATE(), CAST(@sDate as datetime))

SELECT GETDATE(), DATEDIFF (MONTH, GETDATE(), CONVERT(datetime,@sDate))

SELECT GETDATE(), DATEDIFF (MONTH, GETDATE(), CONVERT(datetime,@sDate,111))

-- Seme results for above: 2008-12-29 11:04:51.097 15

-- SQL convert to datetime with wrong style (111 correct, 112 incorrect)

SELECT GETDATE(), DATEDIFF (MONTH, GETDATE(), CONVERT(datetime,@sDate,112))

Above SQL query is very much helpfull for me. I refer this from sqlusa.com

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

Today i tried to restore the backup Database to another SQL Server 2008 R2, but i got the error as

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

Additional information:
The media family on device 'Backupfile.bak' is incorrectly formed. SQL Server cannot process this media family.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)

Reason For Failur

The backup was corrupted. The file was transferred using FTP in text mode rather than binary mode.

Create identity to create table with data

Normally we can't create the identity in the existing column, it must be created while creating the table. Suppose if you have a data in the table you want to add the identity in the table, we can do it in indirect way as below.

Consider the table as table1 and it contain a column as column1

Now am rename the table1 as table1_new as below

sp_rename 'table1','table1_new'

Now create a new table as table1

create table table1(column1 int primary key identity(1,1))

then turn off identity

set identity_insert table1 on

Now copy all data from old table to new table as below

insert into table1(column1 )

select column1 from table1

Now turnon the identity

set identity_insert table1 off

Find the max value of column

select MAX(column1) from table1

Reseed the identity as below

DBCC CHECKIDENT (table1, RESEED, 220);

Now you can make use of the table along with identity as before

How to move the all database associated with Microsoft SharePoint Server 2010

This article describes how to move all of the databases associated with Microsoft SharePoint Server 2010 from one database server to another database server. If your databases are hosted on different servers, this procedure applies to the database server that hosts the configuration database. For moving content databases only, see Move content databases (SharePoint Server 2010). For moving service application databases only, see Rename or move service application databases (SharePoint Server 2010).

For more Click here