Convert char into datetime and calculate days and time elapsed

I have a table with two date field in the format of YYYYMMDDhhmmss. Let's call these fields  timestamp1 and timestamp2. These fields is saved as varchar.I cannot modify the table.
In a view I need I need to find out the elapsed days and time between the two fields. For example:
if timestamp1 = 20041012010202 and timestamp2 = 20041013010103 the output needs to say:
days elapsed: 1 time elapsed: 01 second
I tried converting the fields first by saying: convert(datetime, fieldname, 20). This produces a syntax error. How to I convert these to a proper format (if necessary) and find out the elapsed days and times?
monkeyitAsked:
Who is Participating?
 
gwalkeriqConnect With a Mentor Commented:
Not knowing exactly how you intend to use this, I did not bother making this a function or stored proc, but I show the code below that illustrates a means to accomplish your goals. I took the liberty of shortening the variable names to @s1, @s2 also

The code for determining 1 day + 1 second is also shown. You should change the if (@diff >= 0) to if (@diff > 0) if you want to suppress the hours and minutes. Anyway, you should get the idea I think.

Also, the make 1 day & 1 second, you need to correct the 2nd date string s I've shown below.

Finally, if you look at the definition of convert using format code 20/120, you will perhaps assume that the code below will not work since I don't embed formatting characters for the date. However, I've found  that the format characters for the date are optional in the convert function (but not for the time portion -- go figure)

set nocount on
declare @s1 as varchar(20), @s2 varchar(20)
select @s1='20041012010202', @s2='20041013010203'

declare @t1 datetime, @t2 datetime

select @t1 = convert(datetime, substring(@s1, 1, 8) + ' ' + substring(@s1, 9, 2) + ':' + substring(@s1, 11, 2) + ':' + substring(@s1, 13, 2), 120)
select @t2 = convert(datetime, substring(@s2, 1, 8) + ' ' + substring(@s2, 9, 2) + ':' + substring(@s2, 11, 2) + ':' + substring(@s2, 13, 2), 120)

-- this will show the values in datetime vars
select @t1, @t2

-- convert to days, hours, mins, secs
-- assumes @t1 <= @t2
declare @msg varchar(80)
declare @diff int

select @msg = ''
select @diff = datediff(d, @t1, @t2)
if (@diff >= 0)
begin
  select @msg = @msg + convert(varchar(8), @diff) + ' day(s) '
  select @t1 = dateadd(d, @diff, @t1)
  -- select str(@diff) + ' days', @t1, @t2
end

select @diff = datediff(hh, @t1, @t2)
if (@diff >= 0)
begin
  select @msg = @msg + convert(varchar(8), @diff) + ' hr(s) '
  select @t1 = dateadd(hh, @diff, @t1)
  -- select str(@diff) + ' hrs', @t1, @t2
end

select @diff = datediff(mi, @t1, @t2)
if (@diff >= 0)
begin
  select @msg = @msg + convert(varchar(8), @diff) + ' min(s) '
  select @t1 = dateadd(mi, @diff, @t1)
  -- select str(@diff) + ' mins', @t1, @t2
end

select @diff = datediff(ss, @t1, @t2)
if (@diff >= 0)
begin
  select @msg = @msg + convert(varchar(8), @diff) + ' sec(s) '
  select @t1 = dateadd(ss, @diff, @t1)
  -- select str(@diff) + ' secs', @t1, @t2
end

select @msg




0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.