Select Midnite Record into Cursor

[mccartmd]

Hi:
A base table called V6a.dbf that collects data each hour.
I am trying to figger how to capture every 24th hour record (Midnight) 7 days a week into a cursor. . .to use. . .
What I have is:
SELECT V6a.Prod_pk, V6a.ThisDateTime, V6a.ProdName;
FROM ;
contract1!v6a;
WHERE V6a.ThisDateTime = ( ? )
into Cursor ViewV6Amidnight
browse
(Field of thisdatetime: is datetime)
How can I do this?
Appreciate any feedback
Mike

 

[JRB-Bldr]

From the VFP Help file:
DTOT( ) adds a default time of midnight (12:00:00 A.M.) to the date to produce a valid DateTime value.

However, I'd guess that there might be some variation on the precise timestamp for the record(s) in question.

If not, fine.

But if so then I'd suggest using a DateTime range to find the desired record(s).

Good Luck,
JRB-Bldr

 

[Imaginecorp]

I would not use "Where V6a.ThisDateTime = ( ? )" as you dont know when it fired, even if its off by a few seconds it will not be accurate...
I would create 2 fields in the table one for date() the other for datetime() then use the max(ttoc()) function for datetime field

i.e:

SELECT [B][I]V6a.datefield[/I][/B],V6a.ProdName,V6a.Prod_pk,;
 MAX(TTOC(V6a.ThisDateTime)) as dDatetime,;
FROM contract1!v6a; ;
group by 1,2,3 ;
order by 1 ;
into Cursor ViewV6Amidnight

This will give you the last record for all dates. To be on the safe side I would " SET hour to 24" as well

 

[Olaf Doschke]

If you're only interested in the time, not the date, and if I assume the records for "midnite" are a few seconds off (+/- half a minute) into the previous or next day, then it should be this where clause:

where mod(ThisDateTime-dtot(ttod(ThisDateTime))+30,86400)<60

The used knowledge:
a) the difference of two datetimes is seconds
b) dtot(ttod(datetime)) stripes off the time portion of a datetime
c) because of a) datetime-dtot(ttod(datetime)) is the time portion of the datetime in seconds.

Now acceptable times are either <30 or >86370, but it's easier to shift the value range by 30 seconds and do a modulo operation on that, which will make it only one comparison with <60.

Now in regard of the last 7 days, allow a certain range around datetime()-7*86400-300 to datetime(), eg

where mod(ThisDateTime-dtot(ttod(ThisDateTime))+30,86400)<60
AND ThisDateTime between datetime()-7*86400-300 and datetime()

Bye, Olaf.

 

[Imaginecorp]

sorry; should be

MAX(TTOC(V6a.ThisDateTime,2)) as dDatetime,;

to get the time only...