The following Microsoft SQL Server T-SQL user-defined function will zero the time part of a datetime column:

-- T-SQL return only the date part of datetime, set time part to 0
CREATE FUNCTION dbo.DateOnly

               (@Date DATETIME)

RETURNS DATETIME

AS

  BEGIN

    DECLARE  @DateOnly DATETIME

    SELECT @DateOnly = DATEADD(DD, 0, DATEDIFF(DD, 0, @Date))

    RETURN (@DateOnly)

  END

GO

SELECT dbo.DateOnly(getdate())

-- 2015-03-15 00:00:00.000

The new DATE data type is available SQL Server 2008 and on:

-- SQL Server 2008 DATE data type

SELECT CONVERT (DATE, getdate())

-- 2015-03-15

------------

-- String date only formats

SELECT convert(varchar, getdate(), 101)   -- 03/15/2018     -- MM/DD/YYYY               

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

SELECT convert(varchar, getdate(), 103)   -- 15/03/2018

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

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

SELECT convert(varchar, getdate(), 106)   -- 15 Mar 2018

SELECT convert(varchar, getdate(), 107)   -- Mar 15, 2018

SELECT convert(varchar, getdate(), 111)   -- 2018/03/15

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

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

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(), 11)    -- 18/03/15       -- YY/MM/DD

SELECT convert(varchar, getdate(), 12)    -- 180315         -- YYMMDD