64

Ok - I've asked a few people and there has got to be an easy way to do this.... 

declare @Date date
declare @Time time 
declare @datetime datetime

select @Date = convert(Date,GetDate()) 
select @Time = convert(Time,GetDate())

select @Date, @Time, @Date + @Time (+ operator fails!)

Do I really have to: 1) convert to a string, then convert to datetime field? 2) use DateAdd and DatePart to add hours first then minutes, then seconds.....

codeputer
  • 1,987
  • 3
  • 19
  • 45
  • 5
    I don't think this is a duplicate question, this is a question about the new Date and Time types in SQL 2008. The other question does not involve these at all and is all about DateTime types. – Justin Clarke Aug 20 '13 at 14:01

3 Answers3

87
@Date + cast(@Time as datetime)

In SQL Server 2012 and I assume SQL Server 2014 you neeed to cast both the date and the time variable to datetime.

cast(@Date as datetime) + cast(@Time as datetime)
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • Msg 402, Level 16, State 1, Line 8 The data types date and datetime are incompatible in the add operator. – Davos Mar 04 '14 at 02:12
  • 1
    Agreed, Lookup Error - SQL Server Database Error: The data types date and datetime are incompatible in the add operator. You need to cast both as datetime as pointed out by @jdavies. – mprost Sep 02 '14 at 11:21
  • @mprost Not in SQL Server 2008 but in SQL Server 2012 you are correct. And also presumably in SQL Server 2014. I have updated the answer. – Mikael Eriksson Sep 02 '14 at 11:27
  • 1
    Is there a way to make this work with `datetime2` or other datetime types? – jocull Nov 08 '17 at 14:16
  • 1
    @jocull Have a look at https://dba.stackexchange.com/questions/51440/how-to-combine-date-and-time-to-datetime2-in-sql-server/51443#51443 – Mikael Eriksson Nov 08 '17 at 14:25
13

Try casting them both to DATETIME first:

SELECT CAST(@Date AS DATETIME) + CAST(@Time AS DATETIME)
jdavies
  • 12,784
  • 3
  • 33
  • 33
  • Correct is the non-deterministic form: ALTER TABLE MyTable ADD MyDateTime2 AS ( DATEADD ( day ,DATEDIFF ( day ,CONVERT( DATE, '19000101', 112) , mydate ) ,CONVERT(datetime2(7), mytime) ) ) PERSISTED – Stefan Steiger Oct 06 '16 at 18:56
5

This should work:

select @Date, @Time, CAST(@Date AS datetime) + CAST(@Time AS datetime)
Arun
  • 2,493
  • 15
  • 12