⟩ What Happens If Date-Only Values Are Provided as Date and Time Literals?
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
