Convert field type number to time in SQL Query

[Georgedooley]

Hi
I am trying to create a SQL statement for a crystal report to pull all scantime records from a progress database for the last 2 hours.
The scantime is a interger type field. This is a very large database, therefore I want to write an SQL query to pull back to crystal only the data I need.If I create a formula in crystal to convert the interger to time then this will not be passed in the SQL Query.
Can the interget be converted to a time in SQL to allow the SQL look for all scantime records between currenttime - 2hours?
I hope this makes sense.

 

[IanWaterman]

How is the number related to date. Once you know that it should be possible to write a SQL query.

How is current time defined in Progress, in Oracle its SYSDATE in MS SQL Server its GetDate()

Ian

 

[Georgedooley]

hi Ian
There is a separate date field called scandate, This field type is Date.

DISPLAY STRING(TIME,"HH:MM:SS") is used to diplay this interger in time format with progress

DEFINE VARIABLE hour AS INTEGER.
DEFINE VARIABLE minute AS INTEGER.
DEFINE VARIABLE sec AS INTEGER.
DEFINE VARIABLE timeleft AS INTEGER.
timeleft (24 * 60 * 60) - TIME.
/* seconds till next midnight */
sec timeleft MOD 60.
timeleft (timeleft - sec) / 60.
/* minutes till next midnight */
minute timeleft MOD 60.
/* hours till next midnight */
hour (timeleft - minute) / 60.
DISPLAY "Time to midnight:" hour minute sec .

 

[IanWaterman]

You have completely lost me now!

I am not sure what you want to do.

Ian

 

[IanWaterman]

Please provide example data and what you would want expect.

Ian

 

[Georgedooley]

hi Ian
Sorry for the confusion.
If i create the report just in crystal it is very slow as all the data is returned and filtered locally. I want to write a simple SQL command to allow the filtering be done on the server and only return to crystal the filtered information.
I want 3 fields returned,
-Scantime (interger)
-Scandate (date)
-Ordernum (interger)
I just want data for the last 2 hours. In crystal I would need to use timeserial (0,0,{sortresult.scantime}). This is crystal syntax and is not understood in SQL. This would be very simple if the scantime was field type "time".
I hope this explains a little better

 

[IanWaterman]

I still do not know what you Scantime field is.

I am guessing its seconds from Midnight of the scan date.

If above correct then current time will be

@Currenttime Int

datediff("s", (datetime(year(scandate), month(scandate), day(scandate),0,0,0), currentdatetime)

YOu can then use this in your select

scantime >= datediff("s", (datetime(year(scandate), month(scandate), day(scandate),0,0,0), currentdatetime)
-60

Ian