By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,528 Members | 1,330 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,528 IT Pros & Developers. It's quick & easy.

How to Add Two Date Values in T-SQL

NeoPa
Expert Mod 15k+
P: 31,768
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.
Jun 18 '15 #1

✓ answered by Rabbit

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

Expand|Select|Wrap|Line Numbers
  1. declare @d date
  2. declare @t time
  3.  
  4. set @d = '1/1/2015'
  5. set @t = '9 PM'
  6.  
  7. select DATEADD(SECOND, DATEDIFF(SECOND, 0, @t), CONVERT(datetime, @d))

Share this Question
Share on Google+
10 Replies


Rabbit
Expert Mod 10K+
P: 12,430
You have to convert the date field to datetime first.
Expand|Select|Wrap|Line Numbers
  1. declare @d date
  2. declare @t time
  3.  
  4. set @d = '1/1/2015'
  5. set @t = '9 PM'
  6.  
  7. select CONVERT(datetime, @d) + @t
Jun 18 '15 #2

ck9663
Expert 2.5K+
P: 2,878
Weird....that did not work on my environment. Is it a version thing?

This one did:

Expand|Select|Wrap|Line Numbers
  1. declare @d date
  2. declare @t time
  3.  
  4. set @d = '1/1/2015'
  5. set @t = '9 PM'
  6.  
  7. select cast(cast(@d as varchar(12)) + ' ' + cast(@t as varchar(15)) as datetime2)
  8. --select CONVERT(datetime, @d) + @t
  9.  
~~ CK
Jun 18 '15 #3

Rabbit
Expert Mod 10K+
P: 12,430
Could be a version thing. I'm running SQL Server 2008 R2
Jun 18 '15 #4

NeoPa
Expert Mod 15k+
P: 31,768
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?
Jun 19 '15 #5

Rabbit
Expert Mod 10K+
P: 12,430
If using the addition operator won't work, you can try the dateadd function.

Expand|Select|Wrap|Line Numbers
  1. declare @d date
  2. declare @t time
  3.  
  4. set @d = '1/1/2015'
  5. set @t = '9 PM'
  6.  
  7. select DATEADD(SECOND, DATEDIFF(SECOND, 0, @t), CONVERT(datetime, @d))
Jun 19 '15 #6

NeoPa
Expert Mod 15k+
P: 31,768
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.
Jun 21 '15 #7

Rabbit
Expert Mod 10K+
P: 12,430
No worries, glad I was able to help you find a way forward.
Jun 21 '15 #8

NeoPa
Expert Mod 15k+
P: 31,768
A little weird, but I had to use the following code in order for it to work correctly (certainly from Access) :
Expand|Select|Wrap|Line Numbers
  1. 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.
Jun 21 '15 #9

NeoPa
Expert Mod 15k+
P: 31,768
I didn't expect this to work, but I successfully cut that down to :
Expand|Select|Wrap|Line Numbers
  1. 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 :-)
Jun 21 '15 #10

Rabbit
Expert Mod 10K+
P: 12,430
Yeah, SQL Server is a little odd that way with its one-way only conversions.
Jun 22 '15 #11

Post your reply

Sign in to post your reply or Sign up for a free account.