General MySQL Oracle SQL Server

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

 

⇒ Out-of-Range DATETIME Values in SQL Server Transact-SQL

⇐ Date-Only DATETIME Values in SQL Server Transact-SQL

⇑ Variables and Data Types in SQL Server Transact-SQL

⇑⇑ SQL Server Transact-SQL Tutorials

2017-04-08, 1711🔥, 0💬

General MySQL Oracle SQL Server