Set time to zero in a datetime value

If you have a datetime value that displays in the following format : 2012-11-27 14:53:15.4430000
and you would like it to be displayed this way (time set to "0"): 2012-11-27 00:00:00.0000000

Tip : 

declare @myDate datetime2
select @mydate = DATEADD(DD, 0, DATEDIFF(DD, 0, GETDATE()))

;)

compare 2 tables with different names or schema

Problem:

You have 2 tables that you would like to compare at the column level (retrieve a list of the columns with different data types and/or names).
It happened to me today and the tables had 100+ columns so...

Alternative 1:

if the tables have the same names and a primary key , you can use one hidden gem of SQL Server (since version 90) called tablediff.exe .

Alternative 2:

My problem is the following , both tables belong to the same schema but hold different names. One of the tables is a simple import table (used to import text files) and has no primary key. Archiving the data from the import table to the destination table fails due a potential data truncation.

For the sake  of the example I will a use a database called TestDb that have 2 tables : books and books 1.

As you can see here the only difference is the BookTitle field that has a different length.
Run the following query :

The except statement will select (without duplicates) the elements contained in the left table that are not present in the right table (here books1)
here is the result:

Rgds.