Time-Only DATETIME Values in SQL Server Transact-SQL

Q

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

✍: FYIcenter.com

A

If only time value is provided in a DATETIME variable, the SQL Server will pad the date value with a zero, representing the base date, January 1, 1900. The tutorial exercise below gives you some good examples: 

-- 'hh:mi:ss.mmm' format
DECLARE @x DATETIME;
SET @x = '22:55:07.233';
SELECT @x;
-----------------------
1900-01-01 22:55:07.233

-- 'hh:mi:ss.mmmAM/PM' format
DECLARE @x DATETIME;
SET @x = '10:55:07.233PM';
SELECT @x;
-----------------------
1900-01-01 22:55:07.233

-- 'hh:miAM/PM' format
DECLARE @x DATETIME;
SET @x = '10:55PM';
SELECT @x;
-----------------------
1900-01-01 22:55:00.000

 

⇒Variables and Data Types in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials

2017-04-08, 308👍, 0💬

General MySQL Oracle SQL Server