ClipMate Support Forum

Hi Chris,

I have been trying to make a virtual collection called "Yesterday" with no luck :

select * from clip where
"#DATE#" - 1 <= Clip.TimeStamp AND Clip.TimeStamp <= "#DATE#"
and del = false order by ID;

For inspiration I had a look at this week and this month but to my surprise they have the same sql :

select * from clip where Clip.TimeStamp >= "#DATEMINUSLIMIT#" and del = false order by ID;
select * from clip where Clip.TimeStamp >= "#DATEMINUSLIMIT#" and del = false order by ID;

I don't understand how this can work. Can you put me in the right direction pls?

Kind Regards, Tom

The #DATEMINUSLIMIT# uses another field of the collection properties dialog - it's the "by age of clip - days" purging rule, which is hidden when you're viewing a SQL collection. I know, it sounds dumb... I wanted to recycle that field though.

Use the same SQL as the WEEK query. Now switch the collection properties to "normal" and the form will reveal the date limit. Change that, then switch the collection type back to "virtual". Now it should work.

_________________
I'm the ClipMate Guy!

Hmz I only understand half of this.

Is there a picture/diagram with the SQL Schema and the "magic keywords" like #DATEMINUSLIMIT#

I don't think I can write a query to show only the clips of yesterday using your advice. I think I can write a query showing the last 2 days.
But what I want is *only* yesterday. Does that make sense?

http://www.thornsoft.com/dist/techsupport/db/db70.gif

Date Substitutions:

#DATELASTIMPORT#
#DATELASTEXPORT#
#DATE#
#DATEMINUSLIMIT#

"Limit" is the coll.retentionlimit

_________________
I'm the ClipMate Guy!

Thank you