SQL Question regarding get data between NOW thru 31 days from now

Arc

I am trying to write an SQL statement in Mysql that will get every row from the DB where the date is between today and 31 dyas from now... so basically a whole months worth. What would be the best way to go about this?

Thanks!😃

jnuneznyc

Do you want 31 days or end of month? This will vary depending on which...

31 Days

$endofmonth = strtotime( '31 days' )

End of Month

$endofmonth = strtotime( 'yesterday', strtotime( date( 'm/1/Y', strtotime('next month') ) ) )

Arc

well i need 31 days, the month doesnt matter.

Here is what i have now, but it's not working.

$query2 = "Select intEventID,vchEventTitle,date_format(dtmStartDate,'%m-%d') ";
$query2 .= "as 'StartDate',date_format(dtmEndDate,'%m-%d') as EndDate from $event_table";
$query2 .= "WHERE TO_DAYS(NOW()) + TO_DAYS(dtmStartDate) >=31";
$query2 .= " order by dtmStartDate";

So i need every row from the DB where the date is Between Today and 31 days from now😕 :eek:

jnuneznyc

Use this has your WHERE line...

I have not used TO_DAYS so I am not sure it that function is more efficent.

	$query2 .= "WHERE StartDate >= NOW() AND StartDate <='". date( 'Y-m-d', strtotime('31 days') ) ."'";

Arc

Hmm i see the logic in your statement and it seems ok but the query isn't working. I get the Or die message.

$query2 = "SELECT intEventID,vchEventTitle,date_format(dtmStartDate,'%m-%d')";
$query2 .= "AS 'StartDate',date_format(dtmEndDate,'%m-%d') AS EndDate FROM $event_table";
$query2 .= "WHERE dtmStartDate >= NOW() AND dtmStartDate <='". date( 'Y-m-d', strtotime('31 days') ) ."'";
$query2 .= "ORDER BY dtmStartDate";

Arc

Hmm this here seems as though it should work perfectly but it also returns an error that it cant run the query.

$query = "SELECT intEventID,vchEventTitle,date_format(dtmStartDate,'%m-%d')";
$query .= "AS 'StartDate',date_format(dtmEndDate,'%m-%d') AS EndDate FROM $event_table";
$query .= "WHERE dtmStartDate > NOW() AND (TO_DAYS(dtmStartDate)) < (TO_DAYS(NOW()) + 31)";
$query .= "ORDER BY dtmStartDate";

$result = mysql_query($query2) or die("Unable To Run Query");

The TO_DAYS function turns the current date into the number of days since year 0.

So my SQL is saying select these rows where the date is > Today and the number of days between the startdate and NOW is < 31.

It should atleast run right? At the very least it should just return 0 rows but instead it is erroring out and saying it cant even run the query... hmmm

any ideas? Thanks!

😃

Arc

I figured it out, thanks alot!😃