Date Format

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Date Format

Post 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>
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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)
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Huh?

Post 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
duk
Forum Contributor
Posts: 199
Joined: Wed May 19, 2004 8:45 am
Location: London

Post by duk »

maybe you need

Code: Select all

echo $row['date_assigned'];
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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"]
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

AM I SPEAKING ENGLISH

Post 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!!!
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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]}";

?>
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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;
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

No

Post 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");
User avatar
wtf
Forum Contributor
Posts: 331
Joined: Thu Nov 03, 2005 5:27 pm

Post by wtf »

actually I think duk is correct

in your code you're outputing $date_assigned where it should be $row['date_assigned']
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

WOW

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

icesolid check your PM.

THIS IS YOUR FINAL WARNING.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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)
User avatar
wtf
Forum Contributor
Posts: 331
Joined: Thu Nov 03, 2005 5:27 pm

Post 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.
Post Reply