|
Posted by Gordon Burditt on 11/03/05 01:04
>I have the following select statement whch gives me a count for
>downloads for previous day.
>
>SELECT
> COUNT(swid)
>FROM
> downloads
>WHERE
> date = current_date()-1
^^^^^^^^^^^^^^^^^^^^^^^
This isn't a reasonable way to do date math.
>This works on every day of the month except the 1st day....so on 1st
>November my values for 31 Oct showed 0 even though elsewhere I could see
>that there was activity.
Try running:
select current_date()-40;
and observe that it makes sense on *NO* days of the month.
It's doing INTEGER, not DATE, math.
Then try:
select subdate(current_date(), INTERVAL 40 DAY);
>It was the same on 1 Oct for 30th Sept and
>will be no doubt the same on 1st Dec for 30 Nov.
>
>Any ideas on why it shows zero? Does current_date()-1 have problems
>when it's the first day of month?
current_date()-1 makes about as much sense as current_date()/2.583
(none at all). It's a coding error regardless of what day of the
month it is. Even a stopped clock is sometimes right.
Learn about adddate() and subdate().
Gordon L. Burditt
Navigation:
[Reply to this message]
|