MS SQL Server Concepts and Programming Question:

If only date value is provided in a data and time literal, the SQL Server will pad the time value with a zero, or '00:00:00.000', representing the midnight time of the day. The tutorial exercise below gives you some good examples:

-- 'mm/dd/yyyy' format
DECLARE @x DATETIME;
SET @x = '05/19/2007';
SELECT @x;
GO
2007-05-19 00:00:00.000

-- 'mm.dd.yy' format
DECLARE @x DATETIME;
SET @x = '05.19.07';
SELECT @x;
GO
2007-05-19 00:00:00.000

-- 'yyyy-mm-dd' format
DECLARE @x DATETIME;
SET @x = '2007-05-19';
SELECT @x;
GO
2007-05-19 00:00:00.000

-- 'mon dd, yyyy' format
DECLARE @x DATETIME;
SET @x = 'May 19, 2007';
SELECT @x;
GO
2007-05-19 00:00:00.000

-- 'dd-mon-yyyy' format
DECLARE @x DATETIME;
SET @x = '19-May-2007';
SELECT @x;
GO
2007-05-19 00:00:00.000