How to Add Two Date Values in T-SQL

[NeoPa]

I have a system which stores a date value (Date1) and a time value (From Time) in separate fields and I need to work with the full date/time result (FromDT). How can I add these two values together as this seems to be unsupported by T-SQL?

I also have other time fields ([To Time] & [Break]) which allow me to determine the end date/time (EndDT) by using [Date1] unless [To Time] is less than [From Time] + [Break], in which case [Date1] + 1 day.

All very difficult when (apparently) T-SQL doesn't support adding of dates and/or times together.

{Edit}Apologies for not stating earlier, but I'm using SQL Server 2012-R2.

[Rabbit]

You have to convert the date field to datetime first.

declare @d date
declare @t time

set @d = '1/1/2015'
set @t = '9 PM'

select CONVERT(datetime, @d) + @t

[ck9663]

Weird....that did not work on my environment. Is it a version thing?

This one did:

declare @d date
declare @t time
 
set @d = '1/1/2015'
set @t = '9 PM'
 
select cast(cast(@d as varchar(12)) + ' ' + cast(@t as varchar(15)) as datetime2)
--select CONVERT(datetime, @d) + @t

~~ CK

[Rabbit]

Could be a version thing. I'm running SQL Server 2008 R2

[NeoPa]

Thanks for replying guys.

Unfortunately, 2012 T-SQL seems to be quite restricted on how you can convert date or time values (I've retrospectively updated the OP to indicate the version I'm working in that should have been included earlier).

I may be forced down the CONVERT() via text route, but I'd be lying if I said I were happy at that (Not to denigrate your suggestion CK, for which I'm grateful). Reasons why I don't like that approach are that it is extraordinarily inefficient as far as processing goes, and it's limited in the scope of items it can help with - at least without a lot of creative thought. Consider my second example where I need to check the time before deciding whether or not to add the extra day.

When I say I'd like to add two values together, I mean numerically.

I would guess that it isn't possible due to the restrictions (that I did read up on first), but I have to say you two particularly, have helped me out of seemingly locked corners before so I thought it sensible to ask. If it isn't possible then that's the environment we're in. I don't expect anyone here to be able to redesign SQL Server for us.

PS. I don't suppose you're aware of any function that might allow numerical date manipulation of the sort I'm after?

[Rabbit]

If using the addition operator won't work, you can try the dateadd function.

declare @d date
declare @t time

set @d = '1/1/2015'
set @t = '9 PM'

select DATEADD(SECOND, DATEDIFF(SECOND, 0, @t), CONVERT(datetime, @d))

[NeoPa]

Cheers Rabbit.

That's still very kludgy but could work arithmetically of course. Mix it up with DATEPART() etc and I might have something I can actually use.

Please understand the negative tone reflects on T-SQL rather than you or CK with all this. I know it matches the standards very closely, but having worked in Jet/ACE for so long I guess I'm a little spoiled. Probably just a period of adjustment required I expect. When I worked with T-SQL before I had very little such manipulation to do, ..., and it was with SQL 2000 which was also less tied down in such ways I think.

Anyway, thanks for this. It gives me a way forward.

[Rabbit]

No worries, glad I was able to help you find a way forward.

[NeoPa]

A little weird, but I had to use the following code in order for it to work correctly (certainly from Access) :

DATEADD(DAY,2,DATEADD(SECOND,DATEDIFF(SECOND,0,[From Time]),[Date1]))

I suspect that's something to do with the base date in SQL Server being different from that used by Access (1899-12-30). I'm guessing it uses 1900-01-01, which sort of makes sense really. Not as much as 1899-12-31 of course, but neither seems to want to use that one for some reason.

BTW. I now have that as a Calculated field in my [tblShift] table, which makes good sense as everything that uses that table needs that value.

[NeoPa]

I didn't expect this to work, but I successfully cut that down to :

DATEADD(SECOND,DATEDIFF(SECOND,-2,[From Time]),[Date1])

This seems to indicate that while CONVERTing from date to numeric is disallowed, CONVERTing back the other way isn't. Good news :-)

[Rabbit]

Yeah, SQL Server is a little odd that way with its one-way only conversions.