473,322 Members | 1,614 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

How to Add Two Date Values in T-SQL

NeoPa
32,556 Expert Mod 16PB
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))

10 6084
Rabbit
12,516 Expert Mod 8TB
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
2,878 Expert 2GB
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
12,516 Expert Mod 8TB
Could be a version thing. I'm running SQL Server 2008 R2
Jun 18 '15 #4
NeoPa
32,556 Expert Mod 16PB
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
12,516 Expert Mod 8TB
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
32,556 Expert Mod 16PB
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
12,516 Expert Mod 8TB
No worries, glad I was able to help you find a way forward.
Jun 21 '15 #8
NeoPa
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
12,516 Expert Mod 8TB
Yeah, SQL Server is a little odd that way with its one-way only conversions.
Jun 22 '15 #11

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

Similar topics

16
by: Tim Davidge | last post by:
Hi folks, been a while since I have posted a plea for help and I think I have forgotten everything I learnt from the helpful contributors to this newsgroup, that said however : I'm trying to...
4
by: Joe User | last post by:
Hi all....I have a feeling this is going to be one of those twisted query questions, but here it goes anyways.... I want to generate a report that shows the chronology of events (represented by...
2
by: Keith | last post by:
Good Afternoon, New to .Net. I am trying to pass date/time values to a MS Access query depending on what value is selected from a dropdown list box (January, February, etc). I have declared...
6
by: Dean Slindee | last post by:
I am looking for the "right" way to handle inserting and presenting null date values. Public Const c_NullDate As Date = #12:00:00 AM# If I set the value of a date variable in an SQL Server insert...
5
by: Kermit Piper | last post by:
Hello, I am comparing two date values, one from a database and one that has been converted from a hard-coded string into an actual Date type. So far so good. The problem I'm having is that one...
0
by: Takeadoe | last post by:
First, let me say that I'm brand new to Access, so please assume I know nothing. I've got a table (210k records) of deer harvest information. Date of harvest is one of many variables in the table....
3
by: a | last post by:
In the example below, I'm trying to simply find all the date values in an XML document (the XML is a string in this example) and then add an upper case Z between the last digit and the closing '<'...
5
by: cbalian | last post by:
I am looking for a TSQL code that would calculate a specific date each month that varies based on the following condition: I need the result that would return the date of the Thursday after the...
1
by: SEhtesham | last post by:
Hi, My application is in VS2008 coded in vb.net.I have a table with three columns FromDate,Todate and Schedule.FromDate and ToDate will have date values whereas Schedule will have a any of...
3
by: Benna | last post by:
I’m using VB.06 program. I am trying to do a check details between 2 date values records stored inside database are only start date and end date. For example, I’m searching between 2 datetime...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.