Page 1 of 2

Date Format

Posted: Thu Jan 26, 2006 4:11 pm
by icesolid
I have a date stored in a database in this format ("m-d-Y"). I want to print out the date in a table but only showing the month and day ex: ("m-d"). The code bellow is my attempt so far, not working obviously, the date does not print out:

Code: Select all

<table align="right" width="520" border="0" cellpadding="0" cellspacing="1">
                  <tr>
                    <td align="center" height="30" class="topBar"><b>CONTROL #</b></td>
                    <td align="center" height="30" class="topBar"><b>DATE ORD</b></td>
                    <td align="center" height="30" class="topBar"><b>CUST</b></td>
                    <td align="center" height="30" class="topBar"><b>INSURED</b></td>
                    <td align="center" height="30" class="topBar"><b>LOCATION</b></td>
                    <td align="center" height="30" class="topBar"><b>POLICY #</b></td>
                    <td align="center" height="30" class="topBar"><b>RT</b></td>
                    <td align="center" height="30" class="topBar"><b>ASSGN</b></td>
                  </tr>
                  <?php
                  $result = mysql_query("SELECT control_number,date_ordered,user_code,name_of_insured,location_city,policy_number,survey_type,DATE_FORMAT(date_assigned, '%m-%d') AS date_assigned FROM cases WHERE inspector_code='" . $_POST["inspector_code"] . "' AND sent_to_reviewer='false' ORDER BY control_number ASC");

                  while($row = mysql_fetch_array($result)) {
                      if($row["rush"] == true) {
                          $class = "redBar";
                      } else {
                          $class = "greenBar";
                      }
                  ?>
                  <tr>
                    <td align="center" height="30" class="<?php echo $class; ?>"><b><?php echo $row["control_number"]; ?></b></td>
                    <td align="center" class="<?php echo $class; ?>"><?php echo $row["date_ordered"]; ?></td>
                    <td align="center" class="<?php echo $class; ?>"><?php echo $row["user_code"]; ?></td>
                    <td align="center" class="<?php echo $class; ?>"><?php echo substr($row["name_of_insured"], "0", "15"); ?></td>
                    <td align="center" class="<?php echo $class; ?>"><?php echo substr($row["location_city"], "0", "10"); ?></td>
                    <td align="center" class="<?php echo $class; ?>"><?php echo $row["policy_number"]; ?></td>
                    <td align="center" class="<?php echo $class; ?>"><?php echo substr($row["survey_type"], "0", "2"); ?></td>
                    <td align="center" class="<?php echo $class; ?>">
                    <?php  echo $date_assigned; ?>
                    </td>
                  </tr>
                  <?php
                  }
                  ?>
                  <tr>
                    <td colspan="8"><br>&nbsp;</td>
                  </tr>
                </table>

Posted: Thu Jan 26, 2006 4:17 pm
by raghavan20

Code: Select all

mysql> show create table test3;
+-------+---------------------------------------------------------------------------------------------------------------
----------------------------------+
| Table | Create Table
                                  |
+-------+---------------------------------------------------------------------------------------------------------------
----------------------------------+
| test3 | CREATE TABLE `test3` (
  `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------
----------------------------------+
1 row in set (0.03 sec)

mysql> insert into test3 values(NOW()), (NOW()), (NOW());
Query OK, 3 rows affected (0.13 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test3;
+---------------------+
| date                |
+---------------------+
| 2006-01-26 22:16:49 |
| 2006-01-26 22:16:49 |
| 2006-01-26 22:16:49 |
+---------------------+
3 rows in set (0.03 sec)

mysql> select month(date), year(date) from test3;
+-------------+------------+
| month(date) | year(date) |
+-------------+------------+
|           1 |       2006 |
|           1 |       2006 |
|           1 |       2006 |
+-------------+------------+
3 rows in set (0.01 sec)

Huh?

Posted: Thu Jan 26, 2006 4:18 pm
by icesolid
Whats that supposed to mean?

I'm not talking about sql command prompt. I'm talking about printing it out onto the web in a new format other than the way it was stored! 8O

Posted: Thu Jan 26, 2006 4:23 pm
by duk
maybe you need

Code: Select all

echo $row['date_assigned'];

Posted: Thu Jan 26, 2006 4:28 pm
by raghavan20

Code: Select all

Altered Query:
"SELECT control_number,date_ordered,user_code,name_of_insured,location_city,policy_number,survey_type, day(`date_assigned`) as `day`,  month(`date_assigned`) as `month` FROM cases WHERE inspector_code='" . $_POST["inspector_code"] . "' AND sent_to_reviewer='false' ORDER BY control_number ASC"

use $row["day"], $row["month"]

AM I SPEAKING ENGLISH

Posted: Thu Jan 26, 2006 4:42 pm
by icesolid
duk wrote:maybe you need

Code: Select all

echo $row['date_assigned'];
Am I speaking English?

I want to reformat the result $row["date_assigned"];

I swear people do not read on this forum.

Oh and by the way that "Altered SQL" causes an error, it's no good. Help Please!!!

Posted: Thu Jan 26, 2006 4:56 pm
by raghavan20
sorry, I thought you had timestamp field...if you are storing date as a string in m-d-y format...here is the code to extract day and month from it....

Code: Select all

<?php

$sampleDate = "12-04-2005";
$sampleDate1 = "3-05-2006";
$tempArray = explode("-", $sampleDate);
$tempArray1 = explode("-", $sampleDate1);
echo "<br />Day: {$tempArray[0]}";
echo "<br />Month: {$tempArray[1]}";

echo "<br /><br />Day: {$tempArray1[0]}";
echo "<br />Month: {$tempArray1[1]}";

?>

Posted: Thu Jan 26, 2006 5:00 pm
by pickle
We're all just trying to help - no need to get offensive. You get more bees with honey...

~raghavan20's solution was to modify your query to produce the desired results rather than use PHP to hack it apart. Slightly modifying his final query on his first entry, you should get what you want:

Code: Select all

mysql> select CONCAT(month(date),'-',year(date)) as 'date_assigned' from test3;

+---------------+
| date_assigned |
+---------------+
|        1-2006 |
|        1-2006 |
|        1-2006 |
+---------------+
If, for some reason, you are unable to do that, you can of course pull it apart with PHP:

Code: Select all

$date_returned = "1-26-2006";
list($month,$day,$year) = explode('-',$date_returned);
$formatted_date = $month.'-'.$day;

No

Posted: Thu Jan 26, 2006 5:13 pm
by icesolid
The code above still causes an error.

I want to use MySQL to format it, I know there is a way you just do not know how. I am looking for someone who does. I will just wait until tomorrow.

Using something like the code below I know there is a way:

Code: Select all

$result = mysql_query("SELECT control_number,date_ordered,user_code,name_of_insured,location_city,policy_number,survey_type,DATE_FORMAT(date_assigned, '%m-%d') AS date_assigned FROM cases WHERE inspector_code='" . $_POST["inspector_code"] . "' AND sent_to_reviewer='false' ORDER BY control_number ASC");

Posted: Thu Jan 26, 2006 5:18 pm
by wtf
actually I think duk is correct

in your code you're outputing $date_assigned where it should be $row['date_assigned']

WOW

Posted: Thu Jan 26, 2006 6:30 pm
by icesolid
wtf wrote:actually I think duk is correct

in your code you're outputing $date_assigned where it should be $row['date_assigned']
WHAT BRO?

Uhh...can anyone intelligent help me here?

I WANT TO REFORMAT THE ROW $row["date_assigned"]; FROM ITS CURRENT FORMAT TO A NEW FORMAT AND THEN PRINT IT OUT. SO NO I DO NOT WANT TO PRINT OUT $row["date_assigned"].

Am I the retarded one?

Posted: Thu Jan 26, 2006 6:40 pm
by feyd
that didn't require or call for caps... :?

Help me understand something.. Why do you store the date in a nonstandard way?

Anyways, the explode() routes given by raghavan20 and pickle allow for you to do the format you wish.

Posted: Thu Jan 26, 2006 6:58 pm
by John Cartwright
icesolid check your PM.

THIS IS YOUR FINAL WARNING.

Posted: Fri Jan 27, 2006 6:48 am
by raghavan20

Code: Select all

mysql> select substring('05-23-1999', 1,2) as `month`,  substring('05-23-1999',4,2) as `day`;
+-------+-----+
| month | day |
+-------+-----+
| 05    | 23  |
+-------+-----+
1 row in set (0.01 sec)


mysql> select concat(substring('05-23-1999', 1,2), '-', substring('05-23-1999',4,2));
+------------------------------------------------------------------------+
| concat(substring('05-23-1999', 1,2), '-', substring('05-23-1999',4,2)) |
+------------------------------------------------------------------------+
| 05-23                                                                  |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

Posted: Fri Jan 27, 2006 9:55 am
by wtf
I have a date stored in a database in this format ("m-d-Y"). I want to print out the date in a table but only showing the month and day ex: ("m-d").

Code: Select all

...DATE_FORMAT(date_assigned, '%m-%d') AS date_assigned...
will format date as you want it. It's already a part of your sql statement, therefore $row['date_assigned'] will come out in the format you want.