Combining (concatenating) date and time into a datetime

Question

Using SQL Server 2008, this query works great:

select CAST(CollectionDate as DATE), CAST(CollectionTime as TIME)
from field

Gives me two columns like this:

2013-01-25  18:53:00.0000000
2013-01-25  18:53:00.0000000
2013-01-25  18:53:00.0000000
2013-01-25  18:53:00.0000000
    .
    .
    .

I'm trying to combine them into a single datetime using the plus sign, like this:

select CAST(CollectionDate as DATE) + CAST(CollectionTime as TIME)
from field

I've looked on about ten web sites, including answers on this site (like this one), and they all seem to agree that the plus sign should work but I get the error:

Msg 8117, Level 16, State 1, Line 1
Operand data type date is invalid for add operator.

All fields are non-zero and non-null. I've also tried the CONVERT function and tried to cast these results as varchars, same problem. This can't be as hard as I'm making it.

Can somebody tell me why this doesn't work? Thanks for any help.

What are the original data types for each column?, and if they are strings, how is the data stored there? (YYYY-MM-DD,YYYYMMDD,etc) — Lamak, Sep 04 '13 at 19:43
Actually, follow-up question to you and @Aaron Bertrand, if I'm CASTing (or CONVERTING) my data in the query itself, does it matter if the underlying data is stored as strings or dates? I'm storing as datetime fields, but just curious. — Stanton, Sep 04 '13 at 19:54
@Stanton sure, it does matter. Why go through two levels of cast/convert when you might not need to? — Aaron Bertrand, Sep 04 '13 at 19:57
They were datetimes?. Yes it matters, you can't concatenate datetimes, that's why I first asked the data type — Lamak, Sep 04 '13 at 19:57
Also, the accepted answer assumes that there is no time part on `CollectionDate` (as in, is a date with `00:00:00`). If this is not the case, then it will return wrong results. And the first cast as datetime is unnecessary — Lamak, Sep 04 '13 at 20:00
For some versions of SQL Server, if a CAST to DATETIME is used, it fails. A cast to a DATETIME2 works. I now use @Aaron Bertrand's answer where both data & time are converted to CHAR(). — Kevin Swann, Sep 04 '19 at 11:17

score 63 · Accepted Answer · edited Jun 17 '20 at 15:24

63

Assuming the underlying data types are date/time/datetime types:

SELECT CONVERT(DATETIME, CONVERT(CHAR(8), CollectionDate, 112) 
  + ' ' + CONVERT(CHAR(8), CollectionTime, 108))
  FROM dbo.whatever;

This will convert CollectionDate and CollectionTime to char sequences, combine them, and then convert them to a datetime.

The parameters to CONVERT are data_type, expression and the optional style (see syntax documentation).

The date and time style value 112 converts to an ISO yyyymmdd format. The style value 108 converts to hh:mi:ss format. Evidently both are 8 characters long which is why the data_type is CHAR(8) for both.

The resulting combined char sequence is in format yyyymmdd hh:mi:ss and then converted to a datetime.

edited Jun 17 '20 at 15:24

Kissaki

8,810
5
40
42

answered Sep 04 '13 at 19:47

Aaron Bertrand

272,866
37
466
490

Aaron, thanks for your reply, and insight into my follow up question above would be appreciated. – Stanton Sep 04 '13 at 19:55
Works for sql-server-2012. Nice Job – FirebladeDan Nov 30 '15 at 15:37

score 24 · Answer 2 · answered Jul 19 '16 at 20:00

24

The simple solution

SELECT CAST(CollectionDate as DATETIME) + CAST(CollectionTime as DATETIME)
FROM field

answered Jul 19 '16 at 20:00

Park Keeper

520
4
6

1

Wow - I did not think this would work. Perfect! – Bonez024 Feb 01 '22 at 14:32
1

@Bonex024 - It actually doesn't work "correctly". It works for the example the OP gave because there are no fractional seconds. I might not actually be working correctly for you, either. DATETIME has a resolution of 3.3 milliseconds and can only really handle milliseconds that end with 0, 3, or 7 and so you're losing resolution right off the bat. Give it a value where the MOD(10) value of milliseconds is a 9 and it rounds up to the next time period, which may be just a millisecond away or a year, as is the case with 23:59:59.999 ever since 2016. Implicit conversions "fail" the same way. – Jeff Moden Mar 28 '23 at 20:40
Thanks Jeff. I haven't found a way to combine Date and Time to DateTime without string conversions, and most answers seem to give a rounding issue as you say. I'm using this now, and accepting 2 decimal places on the seconds... `SELECT CONVERT(DATETIME, CONVERT(CHAR(8), CONVERT(DATE,'2023-01-31') , 112) + ' ' + CONVERT(CHAR(11), CONVERT(TIME,'23:59:59.999')))` ... result: `2023-01-31 23:59:59.990` – AjV Jsy Mar 30 '23 at 10:57

score 9 · Answer 3 · edited Jun 08 '16 at 20:44

9

An easier solution (tested on SQL Server 2014 SP1 CU6)

Code:

DECLARE @Date date = SYSDATETIME();

DECLARE @Time time(0) = SYSDATETIME();

SELECT CAST(CONCAT(@Date, ' ', @Time) AS datetime2(0));

This would also work given a table with a specific date and a specific time field. I use this method frequently given that we have vendor data that uses date and time in two separate fields.

edited Jun 08 '16 at 20:44

James Wilson

5,074
16
63
122

answered Jun 08 '16 at 19:32

texasrebelaggie

99
1
2

score 7 · Answer 4 · answered Sep 04 '13 at 19:47

7

Cast it to datetime instead:

select CAST(CollectionDate as DATETIME) + CAST(CollectionTime as TIME)
from field

This works on SQL Server 2008 R2.

If for some reason you wanted to make sure the first part doesn't have a time component, first cast the field to date, then back to datetime.

answered Sep 04 '13 at 19:47

4

`Msg 402, Level 16, State 1, Line 1 - The data types datetime and time are incompatible in the add operator.` – Aaron Bertrand Sep 04 '13 at 19:48
@AaronBertrand, what version of SQL Server are you using? – Sep 04 '13 at 19:50
1

SQL Server 2012. So, it may work for now, but will break when they upgrade. – Aaron Bertrand Sep 04 '13 at 19:51
Odd. Microsoft introduced a breaking change in how date/time values are handled? I wonder if this is affected by any configuration parameters. – Sep 04 '13 at 19:52
Also when I do this: `SELECT CAST(GETDATE() as DATETIME) + CAST(GETDATE() as TIME);` in SQL Server 2008 R2, I get a value back that is 15 hours and 56 minutes in the future, give or take. @Stanton you should validate that this produces *correct data*, not just gets rid of the error, and as I said, this will break in future versions, so I'd be careful where you implement this. – Aaron Bertrand Sep 04 '13 at 19:56
3

Indeed. I recommend @AaronBertrand's answer be accepted. My answer is either flawed right now or will be soon. (Can the accepted answer be changed? I've never tried.) – Sep 04 '13 at 19:57
Aaron's answer accepted. Thanks again to both, the follow-up answer is that I CAN change the underlying table, just trying to educate myself. – Stanton Sep 04 '13 at 20:00
1

Casting both as Date field and Time field as DateTime and adding them together works just fine in SQL 17.9 – PRMan Dec 04 '18 at 18:12

score 1 · Answer 5 · answered Sep 04 '13 at 19:58

DECLARE @ADate Date, @ATime Time, @ADateTime Datetime

SELECT @ADate = '2010-02-20', @ATime = '18:53:00.0000000'

SET @ADateTime = CAST   (
    CONVERT(Varchar(10), @ADate, 112) + ' ' +   
    CONVERT(Varchar(8), @ATime) AS DateTime)

SELECT @ADateTime [A nice datetime :)]

This will render you a valid result.

score 1 · Answer 6 · edited Apr 01 '19 at 07:00

1

dealing with dates, dateadd must be used for precision

declare @a DATE = getdate()
declare @b time(7) = getdate()
select @b, @A, GETDATE(), DATEADD(day, DATEDIFF(day, 0, @a), cast(@b as datetime2(0)))

edited Apr 01 '19 at 07:00

Zoe

27,060
21
118
148

answered Apr 01 '19 at 06:49

Gaurav Aote

51
2
11

Suffers from a rounding issue. select DATEADD(day, DATEDIFF(day, 0, '2023-01-01'), cast('23:59:59.999' as datetime2(0))) result: 2023-01-02 00:00:00 – AjV Jsy Mar 30 '23 at 10:20

László L. L. · Answer 7 · 2020-09-25T18:08:23.790

Solution (1): datetime arithmetic

Given @myDate, which can be anything that can be cast as a DATE, and @myTime, which can be anything that can be cast as a TIME, starting SQL Server 2014+ this works fine and does not involve string manipulation:

CAST(CAST(@myDate as DATE) AS DATETIME) + CAST(CAST(@myTime as TIME) as DATETIME)

You can verify with:

SELECT  GETDATE(), 
        CAST(CAST(GETDATE() as DATE) AS DATETIME) + CAST(CAST(GETDATE() as TIME) as DATETIME)

Solution (2): string manipulation

SELECT  GETDATE(), 
        CONVERT(DATETIME, CONVERT(CHAR(8), GETDATE(), 112) + ' ' + CONVERT(CHAR(8), GETDATE(), 108))

However, solution (1) is not only 2-3x faster than solution (2), it also preserves the microsecond part.

See SQL Fiddle for the solution (1) using date arithmetic vs solution (2) involving string manipulation

This is, by far the best answer in terms of precision and processing efficiency. — DatumPoint, Jul 30 '20 at 06:27

score -1 · Answer 8 · edited Dec 11 '13 at 11:15

drop table test

create table test(
    CollectionDate date NULL,
    CollectionTime  [time](0) NULL,
    CollectionDateTime as (isnull(convert(datetime,CollectionDate)+convert(datetime,CollectionTime),CollectionDate))
-- if CollectionDate is datetime no need to convert it above
)

insert test (CollectionDate, CollectionTime)
values ('2013-12-10', '22:51:19.227'),
       ('2013-12-10', null),
       (null, '22:51:19.227')

select * from test

CollectionDate  CollectionTime  CollectionDateTime
2013-12-10      22:51:19        2013-12-10 22:51:19.000
2013-12-10      NULL            2013-12-10 00:00:00.000
NULL            22:51:19        NULL

same · Answer 9 · 2014-02-20T00:47:29.743

This works in SQL 2008 and 2012 to produce datetime2:

declare @date date = current_timestamp;
declare @time time = current_timestamp;

select 
@date as date
,@time as time
,cast(@date as datetime) + cast(@time as datetime) as datetime
,cast(@time as datetime2) as timeAsDateTime2
,dateadd(dayofyear,datepart(dayofyear,@date) - 1,dateadd(year,datepart(year,@date) - 1900,cast(@time as datetime2))) as datetime2;

score -1 · Answer 10 · edited Aug 22 '19 at 08:52

-1

I am using SQL Server 2016 and both myDate and myTime fields are strings. The below tsql statement worked in concatenating them into datetime

select cast((myDate + ' ' + myTime) as datetime) from myTable

edited Aug 22 '19 at 08:52

B--rian

5,578
10
38
89

answered Aug 22 '19 at 08:06

Uhunghama Harrison

1

score -1 · Answer 11 · edited Feb 17 '20 at 07:00

-1

Concat date of one column with a time of another column in MySQL.

SELECT CONVERT(concat(CONVERT('dateColumn',DATE),' ',CONVERT('timeColumn', TIME)), DATETIME) AS 'formattedDate' FROM dbs.tableName;

edited Feb 17 '20 at 07:00

snieguu

2,073
2
20
39

answered Feb 17 '20 at 05:46

Aditya Sawant

193
1
3

2

The question refers specifically to SQL Server not MySQL – DatumPoint Jul 30 '20 at 06:24

score -3 · Answer 12 · edited Mar 21 '14 at 10:38

-3

SELECT CONVERT(DATETIME, CONVERT(CHAR(8), date, 112) + ' ' + CONVERT(CHAR(8), time, 108))
  FROM tablename

edited Mar 21 '14 at 10:38

Noel

10,152
30
45
67

answered Mar 21 '14 at 10:17

meena

1

1

This is exactly the same as the accepted answer: https://stackoverflow.com/a/18622538/8712471 – MBorg Feb 17 '20 at 06:41

Combining (concatenating) date and time into a datetime

12 Answers12

Linked