Last month -> Current Month -> Next Month

[One]

Hi group -

I want to do a SELECT based on a date range - but mySQL syntax expects
the month range to be two digits.
So I have converted the month to show the leading zero like this :

$next_month = date('m')+1;
$this_month = date('m');
$pre_month = date('m')-1;

However this outputs :
6
06
5

Why does the leading zero get dropped when adding to the date
function ?
Is there a better way to be doing this ?

My select needs to say :
select * from table where date_range is
between ('2008-05%') and ('2008-07%')

[Captain Paralytic]

On 9 Jun, 16:27, One <[e-mail]:

Hi group -

I want to do a SELECT based on a date range - but mySQL syntax expects
the month range to be two digits.
So I have converted the month to show the leading zero like this :

$next_month = date('m')+1;
$this_month = date('m');
$pre_month = date('m')-1;

However this outputs :
6
06
5

Why does the leading zero get dropped when adding to the date
function ?

Because you are not adding to the date function, you are adding to the
OUTPUT of the date function!

Is there a better way to be doing this ?

Yes. See the STR_TO_DATE() function and hen you won't need to use php
for this at all:
http://dev.mysql.com/doc/refman/5.0/...on_str-to-date

[NC]

On Jun 9, 8:27*am, One <[e-mail]:

>
I want to do a SELECT based on a date range - but mySQL
syntax expects the month range to be two digits.
So I have converted the month to show the leading zero
like this :

$next_month = date('m')+1;
$this_month = date('m');
$pre_month = date('m')-1;

However this outputs :
6
06
5

Why does the leading zero get dropped when adding to
the date function ?

Because initially, date() returns a string "06". When you attempt to
add or subtract an integer from that value, it is converted to the
integer 6. The result of the computation is an integer as well.

Is there a better way to be doing this ?

Yes. Here's a possibility:

// Find out current time:
$ts = time();
// Find out what day of the month it is:
$day = date('d', $ts);
// Find out how many days there are in that month:
$days = date('t', $ts);
// Produce the date for the end of the next month:
$next_month = date('Y-m-t', $ts + ($days - $day + 5) * 24 * 60 * 60);
// Produce the date for the beginning of the previous month:
$pre_month = date('Y-m-01', $ts - ($day + 5) * 24 * 60 * 60);
// Create the query:
$query = 'select * from table where date_range is '
. "between ('{$pre_month}') and ('{$next_month}')";

Cheers,
NC

[totalstranger]

On or about 6/9/2008 11:27 AM, it came to pass that One wrote:

Hi group -

I want to do a SELECT based on a date range - but mySQL syntax expects
the month range to be two digits.
So I have converted the month to show the leading zero like this :

$next_month = date('m')+1;
$this_month = date('m');
$pre_month = date('m')-1;

However this outputs :
6
06
5

Why does the leading zero get dropped when adding to the date
function ?
Is there a better way to be doing this ?

My select needs to say :
select * from table where date_range is
between ('2008-05%') and ('2008-07%')

Try this
$next_month = str_pad(date('n')+1,2,"0",STR_PAD_LEFT)
$pre_month = str_pad(date('n')-1,2,"0",STR_PAD_LEFT)

You will also need code to check if $next_month is 12
and $pre_month is < 1
in which case you need to adjust both month and year respectively.