How to combine date from one field with time from

问题:

In an extract I am dealing with, I have 2 datetime columns. One column stores the dates and another the times as shown.

How can I query the table to combine these two fields into 1 column of type datetime?

Dates

2009-03-12 00:00:00.000
2009-03-26 00:00:00.000
2009-03-26 00:00:00.000

Times

1899-12-30 12:30:00.000
1899-12-30 10:00:00.000
1899-12-30 10:00:00.000

回答1:

You can simply add the two.

• if the Time part of your Date column is always zero
• and the Date part of your Time column is also always zero (base date: January 1, 1900)

Adding them returns the correct result.

SELECT Combined = MyDate + MyTime FROM MyTable

Rationale (kudos to ErikE/dnolan)

It works like this due to the way the date is stored as two 4-byte `Integers` with the
left 4-bytes being the `date` and the right 4-bytes being the `time`.  

Its like doing $0001 0000 + $0000 0001 = $0001 0001

Edit regarding new SQL Server 2008 types

Date and Time are types introduced in SQL Server 2008. If you insist on adding, you can use Combined = CAST(MyDate AS DATETIME) + CAST(MyTime AS DATETIME)

Edit2 regarding loss of precision in SQL Server 2008 and up (kudos to Martin Smith)

Have a look at How to combine date and time to datetime2 in SQL Server? to prevent loss of precision using SQL Server 2008 and up.

回答2:

If the time element of your date column and the date element of your time column are both zero then Lieven\'s answer is what you need. If you can\'t guarantee that will always be the case then it becomes slightly more complicated:

SELECT DATEADD(day, 0, DATEDIFF(day, 0, your_date_column)) +
    DATEADD(day, 0 - DATEDIFF(day, 0, your_time_column), your_time_column)
FROM your_table

回答3:

This is an alternative solution without any char conversions:

DATEADD(ms, DATEDIFF(ms, \'00:00:00\', [Time]), CONVERT(DATETIME, [Date]))

You will only get milliseconds accuracy this way, but that would normally be OK. I have tested this in SQL Server 2008.

回答4:

This worked for me

CAST(Tbl.date as DATETIME) + CAST(Tbl.TimeFrom AS TIME)

(on SQL 2008 R2)

回答5:

If you\'re not using SQL Server 2008 (i.e. you only have a DateTime data type), you can use the following (admittedly rough and ready) TSQL to achieve what you want:

DECLARE @DateOnly AS datetime
DECLARE @TimeOnly AS datetime 

SET @DateOnly = \'07 aug 2009 00:00:00\'
SET @TimeOnly = \'01 jan 1899 10:11:23\'


-- Gives Date Only.
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @DateOnly))

-- Gives Time Only.
SELECT DATEADD(Day, -DATEDIFF(Day, 0, @TimeOnly), @TimeOnly)

-- Concatenates Date and Time parts.
SELECT
CAST(
    DATEADD(dd, 0, DATEDIFF(dd, 0, @DateOnly)) + \' \' +
    DATEADD(Day, -DATEDIFF(Day, 0, @TimeOnly), @TimeOnly)           
as datetime)

It\'s rough and ready, but it works!

回答6:

1. If both of your fields are datetime then simply adding those will work.

   eg:

   Declare @d datetime, @t datetime
   set @d = \'2009-03-12 00:00:00.000\';
   set @t = \'1899-12-30 12:30:00.000\';
   select @d + @t
   

2. If you used Date & Time datatype then just cast the time to datetime

   eg:

   Declare @d date, @t time
   set @d = \'2009-03-12\';
   set @t = \'12:30:00.000\';
   select @d + cast(@t as datetime)
   

回答7:

Convert the first date stored in a datetime field to a string, then convert the time stored in a datetime field to string, append the two and convert back to a datetime field all using known conversion formats.

Convert(datetime, Convert(char(10), MYDATETIMEFIELD, 103) + \' \' + Convert(char(8), MYTIMEFIELD, 108), 103) 

回答8:

DECLARE @Dates table ([Date] datetime);
DECLARE @Times table ([Time] datetime);

INSERT INTO @Dates VALUES(\'2009-03-12 00:00:00.000\');
INSERT INTO @Dates VALUES(\'2009-03-26 00:00:00.000\');
INSERT INTO @Dates VALUES(\'2009-03-30 00:00:00.000\');

INSERT INTO @Times VALUES(\'1899-12-30 12:30:00.000\');
INSERT INTO @Times VALUES(\'1899-12-30 10:00:00.000\');
INSERT INTO @Times VALUES(\'1899-12-30 10:00:00.000\');

WITH Dates (ID, [Date])
AS (
    SELECT ROW_NUMBER() OVER (ORDER BY [Date]), [Date] FROM @Dates
), Times (ID, [Time])
AS (
    SELECT ROW_NUMBER() OVER (ORDER BY [Time]), [Time] FROM @Times
)
SELECT Dates.[Date] + Times.[Time] FROM Dates
    JOIN Times ON Times.ID = Dates.ID

Prints:

2009-03-12 10:00:00.000
2009-03-26 10:00:00.000
2009-03-30 12:30:00.000

回答9:

I had many errors as stated above so I did it like this

try_parse(concat(convert(date,Arrival_date),\' \',arrival_time) as datetime) AS ArrivalDateTime

It worked for me.

回答10:

Convert both field into DATETIME :

SELECT CAST(@DateField as DATETIME) + CAST(@TimeField AS DATETIME)

and if you\'re using Getdate() use this first:

DECLARE @FechaActual DATETIME = CONVERT(DATE, GETDATE());
SELECT CAST(@FechaActual as DATETIME) + CAST(@HoraInicioTurno AS DATETIME)

回答11:

SELECT CAST(your_date_column AS date) + CAST(your_time_column AS datetime) FROM your_table

Works like a charm

回答12:

Another way is to use CONCATand CAST, be aware, that you need to use DATETIME2(x) to make it work. You can set x to anything between 0-7 7 meaning no precision loss.

DECLARE @date date = \'2018-03-12\'
DECLARE @time time = \'07:00:00.0000000\'
SELECT CAST(CONCAT(@date, \' \', @time) AS DATETIME2(7))

Returns 2018-03-12 07:00:00.0000000

Tested on SQL Server 14

回答13:

To combine date from a datetime column and time from another datetime column this is the best fastest solution for you:

select cast(cast(DateColumn as date) as datetime) + cast(TimeColumn as datetime) from YourTable

回答14:

SELECT CAST(CAST(@DateField As Date) As DateTime) + CAST(CAST(@TimeField As Time) As DateTime)