Need help calculating time difference from OBDC table

[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

 

[Guest]

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.

 

[scottreds2k]

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.

 

[Guest]

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]

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

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;

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.