Sorry about that. Below, you can see that I have a source and a destination table, with names changed to protect the innocent. The source table has two datetime2(7) fields, and the destination table has one time(7) field. All other fields in the source table are duplicated in the destination table. This is because I am converting the data in the source into the correct format for the destination, which amounts to just copying all fields over except the date/time fields, and for the date/time fields, I want to take the two datetime2(7) dates, get a time span as the difference between the two dates (which as said before is never going to be more than a few hours difference). This time span needs to be stored as a time(7) data type.
So the questions I'm trying to find an answer for are, how do I first get the time span difference between the two, and how do I then get that into a time(7) data format. I would think this should be pretty easy, because in most higher level languages, there are one-liners for doing something like this. But I haven't yet found a one-liner for doing this.
SourceTable {
ID identity int,
StartTime datetime2(7),
EndTime datetime2(7),
SomeField1 int,
SomeField2 varchar(128),
SomeField3 double
}
DestinationTable {
ID identity int,
TimeSpan time(7),
SomeField1 int,
SomeField2 varchar(128),
SomeField3 double
}
SourceTable (data):
ID StartTime EndTime SomeField1 SomeField2 SomeField3
1 2012-04-26 13:53:43.0000000 2012-04-26 13:54:44.5448951 1234 dummytext1 9853.12354
2 2012-10-01 12:00:00.0130952 2012-10-01 17:00:04.9845083 24778 dummytext2 325.0
The above table data needs to be processed so that it becomes the below expected destination data:
DestinationTable (data):
ID TimeSpan SomeField1 SomeField2 SomeField3
1 00:01:01.5448951 1234 dummytext1 9853.12354
2 05:00:04.9714131 24778 dummytext2 325.0
The problems I've run into so far are:
1) I tried using datediff for nanoseconds (since I want to keep the level of precision at 7 places), but I got the error "The datediff function resulted in an overflow". And anyway, I don't really see a way to convert the integer result into time(7) data. An example of what I did to produce this error is as follows:
declare @dt1 datetime2(7) = convert (datetime2, '2012-04-26 13:53:43.0')
declare @dt2 datetime2(7) = convert (datetime2, '2012-04-26 14:54:44.5448951')
select datediff(nanosecond, @dt1, @dt2)
2) I tried merely subtracting the earlier datetime2 from the later datetime2, but got the error "Operand data type datetime2 is invalid for subtract operator". At least with this I would have expected the result to be a datetime2, which I could then just grab the time part from, but it is not allowed for datetime2. And example of this is:
declare @dt1 datetime2(7) = convert (datetime2, '2012-04-26 13:53:43.0')
declare @dt2 datetime2(7) = convert (datetime2, '2012-04-26 14:54:44.5448951')
select datediff(nanosecond, @dt1, @dt2)
3) I am now looking at getting strings for each datetime2 and splitting them to get the hours, minutes, seconds, and nanoseconds strings, and then convert these to integer and do the timespan calculation myself, with hours, minutes, seconds, and nanoseconds as the result, and then creating time(7) data from the result. But this really seems to be so much more work than should be necessary.
Hopefully this is enough information. I'm just thinking it shouldn't be this hard, and so I'm wondering if I'm going in the wrong direction, and if there's just another way that someone would normally do this in transact sql. It's not possible that I could be the first to try this. 🙂
Thanks again...