What Happens If Date-Only Values Are Provided for DATETIME in SQL Server Transact-SQL?

If only date value is provided in a DATETIME variable, 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/2017';
SELECT @x;
-----------------------
2017-05-19 00:00:00.000

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

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

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

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

⇒ Time-Only DATETIME Values in SQL Server Transact-SQL

⇐ Entering 0.001 Second in DATETIME in SQL Server Transact-SQL

⇑ Variables and Data Types in SQL Server Transact-SQL

⇑⇑ SQL Server Transact-SQL Tutorials