I sometimes use the "today" folder, but not really much. It's actually a super convenient feature, especially if your files are all over the place, but I organize my files into proper folders so I have no issues there.
Smartfolders are one of those things that ought to be useful, but somehow never really are. Like Expose (ducking for cover). The way to make them do something is to be obsessive about entering metadata into each file's spotlight comments field. For example, if every collaborator on a document makes sure to enter their names in the spotlight comment each time they work on it, you could have smartfolders like: "Files that John Smith has worked on".
But unless Mac OSX makes adding metadata as comprehensive and ridiculously easy as it was in BeOS and still is in Haiku, it is not going anywhere.