Transforming nvarchar day duration setting into datetime

Question

I have a SQL Server function which converts a nvarchar day duration setting into a datetime value.

The day duration format is >days<.>hours<:>minutes<, for instance 1.2:00 for one day and two hours.

The format of the day duration setting can not be changed, and we can be sure that all data is correctly formatted and present.

Giving the function a start time and the day duration setting it should return the end time.

For instance: 2010-01-02 13:30 ==> 2010-01-03 2:00

I'm using a combination of charindex, substring and convert methods to calculate the value, which is kind of slow and akward. Is there any other way to directly convert this day duration setting into a datetime value?

Answer 1

Not from what I can see. I would end up with a similar bit of SQL like you, using charindex etc. Unfortunately it's down to the format the day duration is stored in. I know you can't change it, but if it was in a different format then it would be a lot easier - the way I'd usually do this for example, is to rationalise the duration down to a base unit like minutes.

Instead of storing 1.2:00 for 1 day and 2 hours, it would be (1 * 24 * 60) + (2 * 60) = 1560. This could then be used in a straightforward DATEADD on the original date (date part only).

With the format you have, all approaches I can think of involve using CHARINDEX etc.

Answer 2

One alternative would be to build a string with the calculation. Then you can run the generated SQL with sp_executesql, specifying @enddate as an output parameter:

declare @startdate datetime
declare @duration varchar(10)
declare @enddate datetime

set @startdate = '2010-01-02 13:30'
set @duration = '0.12:30'

declare @sql nvarchar(max)
set @sql = 'set @enddate = dateadd(mi,24*60*' + 
    replace(replace(@duration,'.','+60*'),':','+') + ', @startdate)'
exec sp_executesql  @sql,
    N'@startdate datetime, @enddate datetime out', 
    @startdate, @enddate out

This creates a string containing set @enddate = dateadd(mi,24*60*0+60*12+30, @startdate) and then runs it.

I doubt this is faster than the regular charindex way:

declare @pos_dot int
declare @day int
declare @hour int
declare @minute int

select 
    @pos_dot = charindex('.',@duration),
    @day = cast(left(@duration, @pos_dot-1) as int),
    @hour = cast(left(right(@duration, 5), 2) as int),
    @minute = cast(right(@duration, 2) as int),
    @enddate = dateadd(mi, 24*60*@day + 60*@hour + @minute, @startdate)