View contents of current date() 2

[mikeisvfp]

Dear Experts,

Because I am working with Appointments i would like for my appointment list to view only appointments of current date.
how would i do this?

 

[Olaf Doschke]

select * from appointments where start between dtot(date()) and dtot(Date()+1) or end between dtot(date()) and dtot(Date()+1) or start<=dtot(date()) and end>=dtot(date())

Would list all appointments that start at some time today, end at sometime today and does not forget appointments starting before today and ending tomorrow or later - appointments spanning over today.

DTOT() is the central function, extending a date to a datetime with 0:00:00 am as time. So dtot(date()) is midnight today (start of today), dtot(date()+1) is tomorrow midnight, end of today.

Bye, Olaf.

 

[Mike Lewis]

If your appointment dates are a Date datatype (as opposed to Datetime), then just compare them with the value returned from DATE().

How you do that depends on how this part of your application is consructed. If you are doing a SQL SELECT to create a cursor, just add a condition to the WHERE clause:

WHERE AppointmentDate = DATE()

Or, use a filter:

SET FILTER TO AppointmentDate = DATE()

And so on.

If the appointment date is a Datetime, use the same code, but convert AppointmentDate (or whatever you call it) to a Date with TTOD().

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[mikeisvfp]

Awesome, Thanks Guys

Just a question, I currently used set filter to (and it works)
becuase I was having a problem with the select statement where it would just run a query in the database table ?

Also what is the real difference between set filter to and Select
I know the Select statement is SQL, but is one faster? Does one have more stability?
Please explain

thanks
Mike

 

[Mike Lewis]

There's quite a big difference.

SET FILTER operates on an existing table or cursor. It doesn't create a new table or cursor. It simply tells VFP to ingore all records that don't meet the filter condition.

So, if you SKIP or SCAN through the table, you will only see those records that meet the condition. The other records are still there of course, but VFP will pretend they don't exist.

With SELECT, you are creating a new table or cursor (more exactly, a new "result set") that only contains the records that meet the condition. Think of it as a copy of the original table, containing just a subset of the records (and fields).

As to which is faster, that depends on what indexes you have. Both options can be optimised, so if the indexes are correct, there shouldn't be any signficant differences in performance. At least, that's the theory. In practice, there are several cases where SET FILTER performs badly and should be avoided.

The usual advice is to favour SELECT over SET FILTER. That's my own choice as well, although it partly comes down to personal preference.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Mike has explained that very well. The only thing I can add is, VFP9 offers a new SQL Clause "WITH BUFFERING" you can use, so that the query contains both what is in the DBF and what is in the buffer you may have activated to not directly write everything into the dbf(s). You can read more about this in the SQL-Select help topic or in the topic about SET SQLBUFFERING.

Bye, Olaf.