Re: [Q] Frusturating SQL question
> Saqib Mausoof <ssmaus@ccmail.monsanto.com> writes:
> I hope someone can help me out of this frustaration. I have a
> tbale in Informix 5.01 that hold information for visitors to our
> site. I need to print out a simple report which shows the number
> of visitors and their time of arrival at midnight everday. A
> simple cron job unloads my query into an ascii file and with a
> little awk manipulation I print the file to the remote site.
>
> Unfortunately, I can't get my SIMPLE query to work. My date
> column is LASTMOD (DATETIME YEAR TO MINUTE) and I need to get
> data for just one day. My query is
>
> Select lname, fname, vendor, status, lastmod
> from visitors
> where lastmod = TODAY (YEAR TO MINUTE)
> order by 3,2,1>
> This does not work, obviously. But what does work? Any ideas?
Try:
select lname, fname, vendor, status, lastmod
from visitors
where lastmod >= today
and lastmod < today + 1
order by 3,2,1
if you run it before midnight, or better:
select lname, fname, vendor, status, lastmod
from visitors
where lastmod >= today - 1
and lastmod < today
order by 3,2,1
if you run it after midnight. After midnight is perhaps better to make
sure every entry is included (also when users visit very late).
When "today" gets automatically extended to compare it to your lastmod
column 0's are inserted for hours and minutes so this works fine.
By the way, don't try using between in the where clause. You will have
trouble avoiding the inclusion of those who visit exactly at midnight
(it can be done, but requires more calculations).
Nils.Myklebust@ccmail.telemax.no
NM-data AS, Aasesvei 71, 1300 Sandvika, Norway
My opinions are those of my company