Programming with dBASE - Filtering last 30 days

Author

Filtering last 30 days

Dan Anderson

2005-11-10, 11:23 am

I want to set a filter to only show rows whose "submittime" is 30 days or
less from the current date. SubmitTime is a character version of the
date/time stamp so that the first 10 characters represent the date, as a
character string. I've tried various expressions for the filter but all
produce an error. I just want to see the items that have come in during the
past 30 days. Can anyone help?

form.rowset = form.quotes1.rowset
form.rowset.indexname = "REFERRED"
xDate =
LEFT(form.quotes1.rowset.fields["submitTime"].value,10)+31
dDate = new date()
form.rowset.filter = [Date() <']+ CTOD(xDate) +[']
form.rowset.refresh()

--
Dan Anderson
UBI Processing Dept.
andersond@ubinc.com
800-444-4824 ext 101

David Kerber

2005-11-10, 11:23 am

In article <vvyIk2g5FHA.1480@news-server>, andersond@ubinc.com says...
> I want to set a filter to only show rows whose "submittime" is 30 days or
> less from the current date. SubmitTime is a character version of the
> date/time stamp so that the first 10 characters represent the date, as a
> character string. I've tried various expressions for the filter but all
> produce an error. I just want to see the items that have come in during the
> past 30 days. Can anyone help?
>
> form.rowset = form.quotes1.rowset
> form.rowset.indexname = "REFERRED"
> xDate =
> LEFT(form.quotes1.rowset.fields["submitTime"].value,10)+31
> dDate = new date()
> form.rowset.filter = [Date() <']+ CTOD(xDate) +[']
> form.rowset.refresh()

Try using curly braces to delimit the date literal:

form.rowset.filter = [Date() < {]+ CTOD(xDate) +[}]

That is how dBase IV delimits dates, and it might work for you as well.

--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

Bruce Beacham

2005-11-10, 11:23 am

Dan Anderson wrote:

Your code doesn't allow for the field value to change as the rowset is
scanned.

How about:

xdate = date() - 31
form.rowset.filter = "submittime > '" + xDate + "'"

dBL's automatic type conversion will take care of the date-to-char.
And if we're lucky the short form of the date will work with the first
part of the timestamp.

Untested, though!!

Bruce Beacham

Ivar B. Jessen

2005-11-10, 11:23 am

On Thu, 10 Nov 2005 10:40:50 -0500, "Dan Anderson" <andersond@ubinc.com>
wrote:

>I want to set a filter to only show rows whose "submittime" is 30 days or
>less from the current date. SubmitTime is a character version of the
>date/time stamp so that the first 10 characters represent the date, as a
>character string. I've tried various expressions for the filter but all
>produce an error. I just want to see the items that have come in during the
>past 30 days. Can anyone help?
>
> form.rowset = form.quotes1.rowset
> form.rowset.indexname = "REFERRED"
> xDate =
>LEFT(form.quotes1.rowset.fields["submitTime"].value,10)+31
> dDate = new date()
> form.rowset.filter = [Date() <']+ CTOD(xDate) +[']
> form.rowset.refresh()

I see some problems in your code.

1) If we assume st = new date(), then ""+DTODT(sT) will be a character
version of a date/time stamp, let us put into a variable, cv = ""+DTODT(sT)

Now let us calculate xDate, xDate = left(cv, 10) + 31

? xDate // 10-11-200531 <--- this is obviously not a legal date

2) The line 'dDate = new date()' is not used later in the code, does it
serve any purpose here?

3) Try to print out the filter string

? [Date() <']+ CTOD(xDate) +[']

The result is: Date() <'- - '

That is not a useable filter.

Ivar B. Jessen

*Lysander*

2005-11-10, 11:23 am

In article <vvyIk2g5FHA.1480@news-server>, andersond@ubinc.com says...
> SubmitTime is a character version of the date/time stamp=20

Do you also have the original version as a Date/Time value?

if so... just use a CAST.

Like (line-wrap!):
form.query1.sql =3D "select * from the_table where cast(the_dtfield as=20
date) >=3D :the_date"
form.query1.params["the_date"] =3D date()-30

or, if you do not like parameters in queries too much (which would be=20
sad...), use a second CAST:

form.query1.sql =3D "select * from the_table where cast(the_dtfield as=20
date) >=3D cast("+dtoc(date()-30)+" as date)"

both methods are already much closer to 'real' SQL and that will make it=20
easier for you to switch to an SQL-database anytime in the future.

modern SQL-servers also usually offer a reserved variable for the actual=20
date.
For example in Firebird-SQL:
"select * from the_table where cast(the_dtfield as date) >=3D=20
(Current_Date-30)"

--=20
ciao,
Andr=E9

Dan Anderson

2005-11-11, 3:23 am

Interesting. I never heard of "cast" before; but, I'm more than willing to
try it. And there's no need to be sad, I am open to parameters, or anything
else that works, in queries. Thanks.

--
Dan Anderson
UBI Processing Dept.
andersond@ubinc.com
800-444-4824 ext 101
"*Lysander*" <nobody@nowhere.com> wrote in message
news:MPG. 1ddd9d8581b3f7f98993
5@news.dbase.com...
In article <vvyIk2g5FHA.1480@news-server>, andersond@ubinc.com says...
> SubmitTime is a character version of the date/time stamp

Do you also have the original version as a Date/Time value?

if so... just use a CAST.

Like (line-wrap!):
form.query1.sql = "select * from the_table where cast(the_dtfield as
date) >= :the_date"
form.query1.params["the_date"] = date()-30

or, if you do not like parameters in queries too much (which would be
sad...), use a second CAST:

form.query1.sql = "select * from the_table where cast(the_dtfield as
date) >= cast("+dtoc(date()-30)+" as date)"

both methods are already much closer to 'real' SQL and that will make it
easier for you to switch to an SQL-database anytime in the future.

modern SQL-servers also usually offer a reserved variable for the actual
date.
For example in Firebird-SQL:
"select * from the_table where cast(the_dtfield as date) >=
(Current_Date-30)"

--
ciao,
André

Sponsored Links