Comparing Dates

[fluffybunny]

I have a date in a database which is in the format DD/MM/YY HH:NN:SS

 

I want to see if this date is more than 'x' days ago, where x is another field in the database

 

 

i.e. DTRecord = 23/4/06 13:06:00

 

AgeRecord=15

 

 

I am guessing I do d DBLookup for the two fields, and then use an evaluate expression and I evaluate this :

 

#$RV_DD/$RV_MM/$RV_YY $RV_HH:$RV_NN:$RV_SS# - #$RV[DBLookup_1_1]# > $RV[ DBLookup_2_1]

 

and this should return true if it is more than 15 days ago, otherwise it will return false.

 

Is this correct?

 

Cheers

 

Simon

[Guest fmonroy]

what kind of database are you using?

 

in most databases you can do something like:

 

select TODAY - DATE_COLUMN from YOUR_TABLE

 

and the result is in days, so you only need to compare with AgeRecord

 

I hope it helps.

 

 

I have a date in a database which is in the format DD/MM/YY HH:NN:SS

 

I want to see if this date is more than 'x' days ago, where x is another field in the database

 

 

i.e. DTRecord = 23/4/06 13:06:00

 

AgeRecord=15

 

 

I am guessing I do d DBLookup for the two fields, and then use an evaluate expression and I evaluate this :

 

#$RV_DD/$RV_MM/$RV_YY $RV_HH:$RV_NN:$RV_SS# - #$RV[DBLookup_1_1]# > $RV[ DBLookup_2_1]

 

and this should return true if it is more than 15 days ago, otherwise it will return false.

 

Is this correct?

 

Cheers

 

Simon

[fluffybunny]

That sounds interesting.

 

I'm using an Access databse and what I want to acheive is the deletion of any records over 15 days old. Each record has a date/time field which has the date/time the record is created. The age of the fields to be deleted (i.e. 15 days) is held in a field in another table.

 

Can I do this all in one fell swoop with a DBlookup module in VG?

[SupportTeam]

Can I do this all in one fell swoop with a DBlookup module in VG?

Should be OK, just setup your SQL statement to correctly do date comparison/selection.

[fluffybunny]

Struggling a bit here......

 

My DB lookup is doing :

 

SELECT RefNumber,Filename,DTRecorded,Status FROM Intray WHERE Status='Read' AND DTRecorded < #$RV_DD/$RV_MM/$RV_YY#-15

 

..but this is selecting all records with Status='Read'

 

I was hoping the -15 at the end would filter out only those from 15 days ago.

 

I attach the trace to show whats occurring.

log.zip

[fluffybunny]

This is doing my head in now......

 

I'm trying it a different way now. As I just want to delete some old records over 5 days old I'm trying this:

 

UPDATE Intray SET Status='Deleted' WHERE DTRead< Now() AND DTRecorded < Now()-5

 

The trace says the update worked ok, but there are records in my database over 5 days old (i.e. DTRecorded is , say, 5/05/2006 16:43:22, DTRead is , say, 10/05/2006 10:00:00, but whose Status is not being changed to Deleted.

 

 

I simply cant see why this wont work. Is it something to do with using the "<" operator?

 

 

Trace included, as always.

0518vgm.zip

[fluffybunny]

Oh what a pillock.

 

If I had looked closer I would have seen that my column in Access was just a tiny bit too small and when I thought DTRecorded was 5/05/2006 it was in fact 15/05/2006 because the leading '1' wasnt being displayed!

 

So, it was working after all. Sorry for wasting everyones time.

 

However....(!!!!)

 

I now cant seem to do a simple thing like delete a file using the RUN module...

 

I am trying to do this :

 

command.com /c del c:\freshdirect\messages\$RV[selectAgedMessage_2_1].wav

 

in my RUN module.

 

$RV[selectedAgedMessage_2_1].wav is a real message, but it doesent want to delete it from c:\freshdirect\messages

 

Probably something simple, but my brain is frazzled now.

[SupportTeam]

try redirecting the output of the DOS command to a file, theat should inform you why the file was not deleted.

[Guest fmonroy]

but it doesent want to delete it from c:\freshdirect\messages

what?