All of us know many techniques to get Datetime part in sql
using Convert , but here I found something interesting which will work
without using Convert .
Take a look
SELECT
CAST( FLOOR( CAST( getDate() AS FLOAT ) ) AS DATETIME )
or
select CAST(
(
STR( YEAR( GETDATE() ) ) + '/' +
STR( MONTH( GETDATE() ) ) + '/' +
STR( DAY( GETDATE() ) )
)
AS DATETIME
)
and
select Dateadd(dd,0,datediff(dd,0,getdate()))
Reference Link:
http://www.bennadel.com/blog/122-Getting-Only-the-Date-Part-of-a-Date-Time-Stamp-in-SQL-Server.htm