Need help calculating time difference from OBDC table

  • Thread starter Thread starter scottreds2k
  • Start date Start date
S

scottreds2k

I am using Access for reporting on data in a table that is controlled
by a separate software program. The start and end date and times are
in separate fields in each record and I need to calculate the
difference between the values. Data format is as follows:

start date: 20060802 (YYYYMMDD) as an integer
start time: 235952 (HHMMSS) 11:59:52 PM as an integer
end date: 20060803 (YYYYMMDD) as an integer
end time: 15 (HHMMSS) 12:00:15 AM as an integer

The difference is 23 seconds.
I know how to change the display format, but have not figured out how
to change the values so that I can calculate the difference in hours
and minutes between start and end.

Any Ideas?

Thanks

Scott
 
Subtracting 20060731 from 20060801 will not result in one day difference.
Subtracting 145815 from 150110 will not give you 2 minutes and 55 seconds.

You need to change your data format to a DateTime datatype field. Add the
new field and run update query.
 
I guess I didn't make myself clear in the first post. I do not have
control over any of the data formats or the way they are written in the
ODBC table I am querying. I have to figure out how to work with what
is written by the outside software. It happens to be PKMS from
Manhattan Associates on an AS400.
 
Try this --
SELECT DateSerial(Left([end date],4),Right(Left([end date],6),2),Right([end
date],2))+CVDate("00000000" & Left(Right("000000" & [end time],6),2) & ":" &
Right(Left(Right("000000" & [end time],6),4),2) & ":" & Right(Right("000000"
& [end time],6),2)) AS [End], DateDiff("s",DateSerial(Left([start
date],4),Right(Left([start date],6),2),Right([start
date],2))+CVDate("00000000" & Left(Right("000000" & [start time],6),2) & ":"
& Right(Left(Right("000000" & [start time],6),4),2) & ":" &
Right(Right("000000" & [start time],6),2)),DateSerial(Left([end
date],4),Right(Left([end date],6),2),Right([end date],2))+CVDate("00000000" &
Left(Right("000000" & [end time],6),2) & ":" & Right(Left(Right("000000" &
[end time],6),4),2) & ":" & Right(Right("000000" & [end time],6),2))) AS
[Difference in seconds]
FROM Scottreds2k;
 
Got there with the same thought process, different formula:

Duration:
1440*((DateSerial((Mid([PREDDT],1,4)),(Mid([PREDDT],5,2)),(Mid([PREDDT],7,2)))+TimeValue(Format([PREDTI],"00\:00\:00")))-(DateSerial((Mid([PRBGDT],1,4)),(Mid([PRBGDT],5,2)),(Mid([PRBGDT],7,2)))+TimeValue(Format([PRBGTI],"00\:00\:00"))))

Never used dateserial/timevalue before

Thanks,
Scott

KARL said:
Try this --
SELECT DateSerial(Left([end date],4),Right(Left([end date],6),2),Right([end
date],2))+CVDate("00000000" & Left(Right("000000" & [end time],6),2) & ":" &
Right(Left(Right("000000" & [end time],6),4),2) & ":" & Right(Right("000000"
& [end time],6),2)) AS [End], DateDiff("s",DateSerial(Left([start
date],4),Right(Left([start date],6),2),Right([start
date],2))+CVDate("00000000" & Left(Right("000000" & [start time],6),2) & ":"
& Right(Left(Right("000000" & [start time],6),4),2) & ":" &
Right(Right("000000" & [start time],6),2)),DateSerial(Left([end
date],4),Right(Left([end date],6),2),Right([end date],2))+CVDate("00000000" &
Left(Right("000000" & [end time],6),2) & ":" & Right(Left(Right("000000" &
[end time],6),4),2) & ":" & Right(Right("000000" & [end time],6),2))) AS
[Difference in seconds]
FROM Scottreds2k;


scottreds2k said:
I guess I didn't make myself clear in the first post. I do not have
control over any of the data formats or the way they are written in the
ODBC table I am querying. I have to figure out how to work with what
is written by the outside software. It happens to be PKMS from
Manhattan Associates on an AS400.
Click to expand...
Click to expand...
 
Back
Top