Another MySQL date question

[JDJones]

I am entering some older things in a database for the first time.
Included in the information I need to enter is the month and year the
item was submitted. Ideally I'd like to be able to enter the old dates
into a field like 03/98 or 12/02 or and still be able sort them by
descending or ascending order.

Is there a relatively easy way to do this? and what kind of field (date,
text, etc) does it need to go into?

Thanks

[Pedro Graca]

JDJones wrote:

I am entering some older things in a database for the first time.
Included in the information I need to enter is the month and year the
item was submitted. Ideally I'd like to be able to enter the old dates
into a field like 03/98 or 12/02 or and still be able sort them by
descending or ascending order.

Is there a relatively easy way to do this? and what kind of field (date,
text, etc) does it need to go into?

I'd put that into a `datetime` column with the day always being 1.

If you don't want that, the second choice would be *two* columns; one
for the year and another for the month, both "int".

Depending on your specific needs, formatting would then be done either
within the select or through php.
--
--= my mail box only accepts =--
--= Content-Type: text/plain =--
--= Size below 10001 bytes =--

[Geoff Berrow]

I noticed that Message-ID: <euQ0c.434100$I06.4906188@attbi_s01> from
JDJones contained the following:

I am entering some older things in a database for the first time.
Included in the information I need to enter is the month and year the
item was submitted. Ideally I'd like to be able to enter the old dates
into a field like 03/98 or 12/02 or and still be able sort them by
descending or ascending order.

Pick an arbitrary date in the middle of the month and add that to the
entry. e.g

$date= $year."/".$month."/15"

I may well store the date in UNIX format, data type integer, mainly
because I'm finding them slightly easier to work with.

Say you have

$year="97"; $month="03";

Get the date like this
$unixdate= strtotime($year.$month."15");

to display the date

print date("F, Y",$unixdate);
--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[JDJones]

Geoff Berrow wrote:

I noticed that Message-ID: <euQ0c.434100$I06.4906188@attbi_s01> from
JDJones contained the following:

I am entering some older things in a database for the first time.
Included in the information I need to enter is the month and year the
item was submitted. Ideally I'd like to be able to enter the old dates
into a field like 03/98 or 12/02 or and still be able sort them by
descending or ascending order.

Pick an arbitrary date in the middle of the month and add that to the
entry. e.g

$date= $year."/".$month."/15"

I may well store the date in UNIX format, data type integer, mainly
because I'm finding them slightly easier to work with.

Say you have

$year="97"; $month="03";

Get the date like this
$unixdate= strtotime($year.$month."15");

to display the date

print date("F, Y",$unixdate);

After experimenting with you suggestion and Pedro's, I think the easiest
for me to use would be using the data type "date" column.

What if I decide to store it like that using the defaulted "2000-09-15"
format. How can I pull the numbers out to get it to print the result as
"09-2000" ?

[Pedro Graca]

JDJones wrote:

What if I decide to store it like that using the defaulted "2000-09-15"
format. How can I pull the numbers out to get it to print the result as
"09-2000" ?

Do it either on the SQL side:
( see http://www.mysql.com/doc/en/Date_and...functions.html )

mysql> select date_format(now(), '%m-%Y');
+-----------------------------+
| date_format(now(), '%m-%Y') |
+-----------------------------+
| 03-2004 |
+-----------------------------+
1 row in set (0.00 sec)
or on the PHP side:
( see http://www.php.net/date )

echo date('m-Y', $db_date);
--
--= my mail box only accepts =--
--= Content-Type: text/plain =--
--= Size below 10001 bytes =--

[JDJones]

Pedro Graca wrote:

JDJones wrote:

What if I decide to store it like that using the defaulted "2000-09-15"
format. How can I pull the numbers out to get it to print the result as
"09-2000" ?

Do it either on the SQL side:
( see http://www.mysql.com/doc/en/Date_and...functions.html )

mysql> select date_format(now(), '%m-%Y');
+-----------------------------+
| date_format(now(), '%m-%Y') |
+-----------------------------+
| 03-2004 |
+-----------------------------+
1 row in set (0.00 sec)
or on the PHP side:
( see http://www.php.net/date )

echo date('m-Y', $db_date);

I can't get either one to work. :(

I have the dates entered in a db date column called "submitted". It is
in the 0000-00-00 format. I am executing the query like: $query =
"select * from table where category = 'text' order by submitted DESC";

Then, cutting out extraneous portions of the script I am printing it by
doing this:

<?php
while ($row= mysql_fetch_array($result)) {
$title1 = $row["text"];
$title2 = $row["submitted"];
?>

<table border="2" width="100%" cellspacing="2" callpadding="0"
class="tableblue">
<tr>
<td width="100%"><? echo "<br><span
class=\"number\">$count.)</span>&nbsp;$title1&nbsp;$title2<br><br>"?></td>
</tr>
</table>

I can't get either of your two suggested methods to work. The echo
date('m-Y', $db_date); one output the correct format I was seeking when
I modified it - echo date('m-Y', $title2); - but everything was coming
up 12-1969 no matter what the dates were that were actually in the
field. What am I still doing wrong?

[Geoff Berrow]

I noticed that Message-ID: <9%91c.463139$na.1097057@attbi_s04> from
JDJones contained the following:

I can't get either of your two suggested methods to work. The echo
date('m-Y', $db_date); one output the correct format I was seeking when
I modified it - echo date('m-Y', $title2); - but everything was coming
up 12-1969 no matter what the dates were that were actually in the
field. What am I still doing wrong?

You should have paid more attention to my reply <g>

echo date('m-Y', strtotime($title2));
--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[JDJones]

Geoff Berrow wrote:

I noticed that Message-ID: <9%91c.463139$na.1097057@attbi_s04> from
JDJones contained the following:

I can't get either of your two suggested methods to work. The echo
date('m-Y', $db_date); one output the correct format I was seeking when
I modified it - echo date('m-Y', $title2); - but everything was coming
up 12-1969 no matter what the dates were that were actually in the
field. What am I still doing wrong?

You should have paid more attention to my reply <g>

echo date('m-Y', strtotime($title2));

(*hangs head sheepishly*)

You're right Geoff, I should have. I guess seeing the $unixdate scared
me off. ;)

It's now working and I thank you.

[Geoff Berrow]

I noticed that Message-ID: <nnb1c.26112$ko6.231064@attbi_s02> from
JDJones contained the following:

echo date('m-Y', strtotime($title2));
(*hangs head sheepishly*)

he he...
You're right Geoff, I should have. I guess seeing the $unixdate scared
me off. ;)
It's just a number. The number of seconds since 1st January 1970.
Nothing scary really. :-)
It's now working and I thank you.

Cool, no problem.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/