Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Convert char into datetime and calculate days and time elapsed

Posted on 2004-10-19
Medium Priority
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?
Question by:monkeyit
1 Comment

Accepted Solution

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)
  select @msg = @msg + convert(varchar(8), @diff) + ' day(s) '
  select @t1 = dateadd(d, @diff, @t1)
  -- select str(@diff) + ' days', @t1, @t2

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

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

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

select @msg


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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 …
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.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

572 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