Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Convert char into datetime and calculate days and time elapsed

Posted on 2004-10-19
3
Medium Priority
?
532 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 500 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

636 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