By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,423 Members | 1,601 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,423 IT Pros & Developers. It's quick & easy.

Last month -> Current Month -> Next Month

P: n/a
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%')

Jun 27 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On 9 Jun, 16:27, One <david.hun...@gmail.comwrote:
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
Jun 27 '08 #2

P: n/a
NC
On Jun 9, 8:27*am, One <david.hun...@gmail.comwrote:
>
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
Jun 27 '08 #3

P: n/a
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.
Jun 27 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.