MS SQL Server Concepts and Programming Question:
Download Job Interview Questions and Answers PDF
What Happens If Date-Only Values Are Provided as Date and Time Literals?
Answer:
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
-- '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
Download MS SQL Server Interview Questions And Answers
PDF
Previous Question | Next Question |
Why I Can Not Enter 0.001 Second in Date and Time Literals in MS SQL Server? | What Happens If Time-Only Values Are Provided as Date and Time Literals? |