Solved

# Convert char into datetime and calculate days and time elapsed

Posted on 2004-10-19
524 Views
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?
0
Question by:monkeyit
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 6

Accepted Solution

gwalkeriq earned 125 total points
ID: 12353711
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be \$37.1B.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
###### Suggested Courses
Course of the Month5 days, 2 hours left to enroll