DISPLAY DATETIME WITHOUT HOURS, MINUTES, AND SECONDS

To see the datetime displayed without the hours, minutes, and seconds,
you can use a default constraint to drop the time granularity of the
datetime data type. You can accomplish this by using the format option 
with
the datetime data type in conjunction with the CONVERT function. Within 
SQL
or a stored procedure, the CONVERT function with the datetime format
option will display the datetime without the time information.

To use the datetime format in conjunction with the CONVERT function,
create a table with two columns. One column will have the datetime data 
type
and will, by default, enforce granularity to the current day within SQL
Server. The second column is another datetime data type with SQL 
Server's
current day and time as the default.

CREATE TABLE test
(create_date  DATETIME NOT NULL
      DEFAULT CONVERT(DATETIME, CONVERT(CHAR(10),
      CURRENT_TIMESTAMP, 110)),
 create_datetime   DATETIME NOT NULL
      DEFAULT CURRENT_TIMESTAMP)
GO

The next step is to insert default values into the table.

INSERT test VALUES ( DEFAULT, DEFAULT )
GO

Finally, select the default values from the table. You should see the
difference between the granularity of the displayed values and the 
datetime
data type columns.

SELECT * FROM test
GO

To clean up this test, drop the test table.

DROP TABLE test
GO

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