getting this week and last week

[Moeis]

Hi,

I need to display data as it was logged Mon-Fri, this week and last week, but can't work out the SQL.

For this week, I can get WHERE TO_DAYS(NOW()) - TO_DAYS(Date) < 7

...but of course this means that if it's only Tuesday I'll still get the Friday of last week.

Any ideas?

Lee

[raf]

yeah. i'd first compute the datavelues for the start and endday of your period, and then insert them into the where clause.
how? depends on the scriptinglanguage you're using. (PHP, ASP, ...) Here's a thread for an asp sollution.
http://www.codingforums.com/showthread.php?s=&threadid=20079&highlight=jump
(look for post of 05-16-2003)You probably need to change it for your needs or make something similar if you use PHP, but you'll see what i mean.

[ConfusedOfLife]

It might look rediculous, coz I never put a date into a MySQL db, but what if we don't use the MySQL db date field (we should have something like this, right?!) and just use 3 int fields, for the Year, Month and day. Then we can easily search for whatever we want and I don't think we have any problem, huh?! Suppose that you're looking for 1999-2-5, so, a simple query like:


$query = "select * from myDB where year = 1999 and month = 2 and day = 5"


would do the trick, huh?!

[raf]

ConfusedOfLife,

And how would you run a query like
select * from table where enddate between '2003-02-18' and '2003-05-20'

Splitting it up in three variabels is sometimes safer since you rule out problems caused by different reginal settings, but i woudn't do it if you select on the dates

[ConfusedOfLife]

select * from myTable where (Year = 2003) and (Month < 5 and Month > 2 ) and ( Day < 20 and Day > 18 )


I didn't try it but I think it should work!

[raf]

Oh yeah? Just try it:D
It wount (and i didn't try it --> the month < 5 is not correct + the days are absolutely not correct. In fact, you need all days, cause 2003-04-24 should also be selected)
So you would need something far more complex. But maybe you'll find something. Keep me informed:)

[ConfusedOfLife]

Haha! Ok, I'll try but the way you speak makes me sure that it doesn't work! Well, mathematically it looks fine, but I think I have to study more, yeah, I do have to study more!

[ConfusedOfLife]

Originally posted by raf
Oh yeah? Just try it:D
It wount (and i didn't try it --> the month < 5 is not correct + the days are absolutely not correct. In fact, you need all days, cause 2003-04-24 should also be selected)
So you would need something far more complex. But maybe you'll find something. Keep me informed:)

Ok, what about this one?! :D


<?PHP

# The first date should always be prior to the second date.
$first_date = array( "year" => 1999,
"month" => 5,
"day" => 22
);

$second_date = array( "year" => 2002,
"month" => 2,
"day" => 15
);

$query = "select * from my_table where ( ( Year > {$first_date['year']} AND Year < {$second_date['year']} ) OR" .
" ( Year = {$first_date['year']} AND Month > {$first_date['month']} ) OR" .
" ( Year = {$first_date['year']} AND Month = {$first_date['month']} AND Day > {$first_date['day']} ) OR" .
" ( Year = {$second_date['year']} AND Month < {$second_date['month']} ) OR" .
" ( Year = {$second_date['year']} AND Month = {$second_date['month']} AND Day < {$second_date['day']} ) )";

?>

[Weirdan]

Originally posted by Moeis
Hi,

I need to display data as it was logged Mon-Fri, this week and last week, but can't work out the SQL.
..... [skipped]
Any ideas?

Lee

hm... may be something like this:

.... where TO_DAYS(NOW()) - TO_DAYS(Date) < (7+WEEKDAY(NOW()))

[raf]

ConfusedOfLife,

That looks correct. And so simple ! ;) What's worse, the cure or the disease ? (What if we make it a date-time? Or if we set the borders by adding/subtracting the interval from a date in the db.)

[ConfusedOfLife]

Originally posted by raf
ConfusedOfLife,

That looks correct. And so simple ! ;) What's worse, the cure or the disease ? (What if we make it a date-time? Or if we set the borders by adding/subtracting the interval from a date in the db.)

I am worse my friend, I am worse! :D Ok, I admit, well, I have to admit! You're right, but you asked for that thing, I wrote it!

[raf]

Hehehehe :D Just poking you a bit. Anyway, splitting it up into several variabels may seem a good idea, until you start selecting on them.
I've seem tables at work where they did it, but just for registering.

But i think Moeis has left the building ...

[Moeis]

Thanks ppl!

Now trying...

WHERE WEEK(Date)=WEEK(NOW())

..and...

WHERE WEEK(Date)=WEEK(NOW())-1

...will hopefully do the trick.