mysql query to find weekends within two dates

Codewalkers Forums Sponsor:

You eat, breathe and sleep innovation. Build your mobile intelligence with BlackBerry® experts this July. Register Today!

November 5th, 2003, 07:44 AM

kathiramu

Join Date: Apr 2007

Posts: 6

Time spent in forums: < 1 sec
Reputation Power: 0

mysql query to find weekends within two dates

hi,
i need help in writing a query to find the number of saturdays and sundays falling within a time period(two dates).
thnks
ramu

November 5th, 2003, 01:20 PM

aj2taylo

Join Date: Apr 2007

Location: Mississauga, ON, Canada

Posts: 61

Time spent in forums: 26 sec
Reputation Power: 2

RE: mysql query to find weekends within two dates

What are your dates stored as? Timestamp? Date?

November 5th, 2003, 01:35 PM

honcho

Contributing User

Codewalkers Beginner (1000 - 1499 posts)

Join Date: Apr 2007

Location: Cape Cod

Posts: 1,347

Time spent in forums: 3 h 52 m 2 sec
Reputation Power: 3

RE: mysql query to find weekends within two dates

I'm not quite sure what you are asking. Are you try to find the number of Saturdays and Sundays between two dates, or trying to find the number of Saturdays and Sundays that are stored in some table in a database between two dates.

If it's the first one, do it in PHP, not MySQL.

If it's the second one, try this:

Code:

SELECT COUNT(*) FROM table_name
WHERE date_column BETWEEN '2003-10-01' AND '2003-11-01'
AND WEEKDAY(date_column) > 4

November 5th, 2003, 10:54 PM

kathiramu

Join Date: Apr 2007

Posts: 6

Time spent in forums: < 1 sec
Reputation Power: 0

RE: mysql query to find weekends within two dates

hi,
i am happy to see the reply to my posting here.
1. i am using Date filed to store dates.
2. i am trying find the number of Saturdays and Sundays between two dates stored in a table.
thanks.
ramu

November 10th, 2003, 04:08 PM

CodeKadiya

Join Date: Apr 2007

Location: Colombo,Sri Lanka

Posts: 2,313

Time spent in forums: < 1 sec
Reputation Power: 4

RE: mysql query to find weekends within two dates

How about this? There may be a way of achieving the same result in a shoter way.

php Code:

Original - php Code

<?php   
$fromDate='10/1/2003'; // Start of date range
$toDate='11/30/2003'; // End of date range
 
$arrFrom=explode('/',$fromDate);
$arrTo=explode('/',$toDate);
 
$numSundays=0;
$numSaturdays=0;
 
$fromDate=mktime(0,0,0,$arrFrom[0],$arrFrom[1],$arrFrom[2]);
$toDate=mktime(0,0,0,$arrTo[0],$arrTo[1],$arrTo[2])+(60*60*24);
while($fromDate<>$toDate)
{
    if(date("w",$fromDate)==0)  $numSundays++;
    else if(date("w",$fromDate)==6) $numSaturdays++;
    $fromDate=$fromDate+(60*60*24);
}
 
echo "There are <b>$numSundays</b> Sundays and <b>$numSaturdays</b> Saturdays between given date range.";
?>

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: