TIME DIFFERENCE IN SECONDS

Hi guys,

I have a field on a DB2 database that has the date and time a field was created in the following format:

YYYYMMDDhhmmss

for example, for may 9th 2006 at 2:43:23 PM the field has something like

20060509144323 (note 2 pm is changed by 14, to european format).

I need to know for each field the time difference in seconds comparing with the current date and time - now() -

How can I do that?!

TKS
pvg1975Asked:
Who is Participating?
 
coderbluesConnect With a Mentor Commented:
Then something like this to convert it:

y = "20060509144323"

vyear = Left(y, 4)
vmonth = Mid(y, 5, 2)
vday = Mid(y, 7, 2)
vhour = Mid(y, 9, 2)
vmin = Mid(y, 11, 2)
vsec = Right(y, 2)

actdate = CDate(vmonth + "/" + vday + "/" + vyear)
actdate = actdate + CDate(" " + vhour + ":" + vmin + ":" + vsec)
0
 
coderbluesCommented:
Did you try the datediff funx?

datediff("s",t1,t2)

For ex:
datediff("s","1:59:03 PM","2:00:00 PM")
0
 
pvg1975Author Commented:
Yes, but the format of the date is not a date/time format value:

20060509144323
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
fostejoConnect With a Mentor Commented:
pvg1975,

Try parsing/converting what you get from the DB2 database into a suitable format to convert to a 'proper' date with CDATE(), prior to using the method sugessted by coderblues.

I haven't tested but something like this rough example perhaps:

sODate="20060509144323"    ' Just an example, should be the actual data grabbed from the database..

sYear=left$(sODate,4)
sMonth=mid$(sODate,5,2)
sDay=mid$(sODate,7,2)

sHour=mid$(sODate,9,2)
sMin=mid$(sODate,11,2)
sSec=right$(sODate,2)

sNewDate=sMonth&"/"&sDay&"/"&sYear&" "&sHour&":"&sMin&":"&sSec   ' gives something like "05/09/2006 14:43:23" which CDate should understand.

lSecDiff=DateDiff("s",CDate(sNewDate),now())


cheers,
0
 
fostejoCommented:
Great minds coderblues!
0
 
lunchbyteCommented:
I am not sure this will help but here is a function that I use.

Function ElapsedTime(tStart As Variant, tStop As Variant) As String
' **************************************************************
'Notes:         : Times passed to this function should be
'               : in valid format (e.g., hh:mm.ss).  Otherwise,
'               : function will return 0:00:00
'    ElapsedTime(Format(str, "hh:mm:ss"), Format(Now, "hh:mm:ss"))
' ****************************************************************


On Error GoTo END_ELAPSEDTIME

    Dim dtr, dtl, jml As Long

    dtl = (Hour(tStart) * 3600) + (Minute(tStart) * 60) + (Second(tStart))

    dtr = (Hour(tStop) * 3600) + (Minute(tStop) * 60) + (Second(tStop))

    If tStop < tStart Then
       jml = 86400
    Else
       jml = 0
    End If
    jml = jml + (dtr - dtl)
   
    ElapsedTime = Format(str(Int((Int((jml / 3600)) Mod 24))), "00") + ":" + Format(str(Int((Int((jml / 60)) Mod 60))), "00") + ":" + Format(str(Int((jml Mod 60))), "00")

END_ELAPSEDTIME:
     
End Function
0
 
lunchbyteCommented:
Oh yeah, you can change the format to match yours or just change yours to what the function use.
0
 
pvg1975Author Commented:
Thanks guys!
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.