Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate the combination of date and time.
-- Using the + operator
DECLARE @Date datetime = '2016-10-23', @Time datetime = '1900-01-01 12:15:44';
SELECT CombinedDatetime = @Date+@Time;
-- 2016-10-23 12:15:44.000
------------
The fnCombineDateTime UDF (scalar-valued user-defined function) combines string date and time into a datetime result:
USE AdventureWorks;
GO
-- SQL create function to combine data and time into datetime
CREATE FUNCTION fnCombineDateTime (@Date char(8), @Time char(6))
RETURNS datetime
AS
BEGIN
DECLARE @DateTime datetime
SELECT @DateTime=CONVERT(datetime, @Date + ' ' +
STUFF(STUFF(@Time, 3, 0, ':'), 6, 0, ':'))
RETURN @DateTime
END
GO
-- Test string to datetime conversion
DECLARE @Date char(8), @Time char(6)
SET @Date='20121225'
SET @Time='094400'
SELECT [Date Time] = dbo.fnCombineDateTime (@Date, @Time)
GO
/* Result
Date Time
2012-12-25 09:44:00.000
*/
-------------
-------------
-- SQL Server 2008 Combine Date and Time (7) into datetime2(7)
-------------
USE AdventureWorks2008;
DECLARE @Date DATE,
@Time TIME(7),
@Date7 DATETIME2(7)
SELECT @Date = CONVERT(VARCHAR,GETDATE(),112),
@Time = '20:30:40.9876543'
-- Map to 1900-01-01
SET @Date7 = @Time
SELECT Date7=@Date7, [Date]=@Date,
Combination=DATEAdd(DAY,datediff(DAY,@Date7,@Date),@Date7)
/*
Date7 Date Combination
1900-01-01 20:30:40.9876543 2012-07-31 2012-07-31 20:30:40.9876543
*/
------------
Related articles:
http://www.sqlusa.com/bestpractices/datetimeconversion/
How to combine date from one field with time from another field - MS SQL Server
|
