Solved

Convert char into datetime and calculate days and time elapsed

Posted on 2004-10-19
3
524 Views
Last Modified: 2012-05-05
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
Comment
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
  • Learn & ask questions
3 Comments
 
LVL 6

Accepted Solution

by:
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

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.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question