0

I am trying to change the format of how the date appears on the output from my form. I have searched the internet and I believe this is what I need to do:

SELECT datetime(date_submitted,'%m/%d/%Y') as date_submitted FROM guestquestionnaire
  • date_submitted is the name of this element in my database
  • datetime is the type of element
  • guestquestionnaire is the name of my table

But it's giving me a blank page as though something is wrong. Any ideas? Please let me know if you need more code. I am using PDO and I ideally want the date format at mm/dd/yyyy. If I can also get the time format as 11:11 am (as an example) that would be great too!!

Update - Code:

<?php try { $handler = new PDO('mysql:host=localhost;dbname=***', '***', '***'); $handler->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch(PDOException $e) { echo $e->getMessage(); die(); } class guestquestionnaireEntry { public $id, $date_submitted, $entry; public function __construct() { $this->entry = " <tr style='font-size: 8pt;'><td width='60%'><a href=\"?ID={$this->ID}\">ID</a> </td><td width='40%' colspan='2'>{$this->date_submitted}</td></tr> <table border='1' align='center'> <tr style='background: #566890; font-size: 8pt; font-weight: bold; color: #fff;'><td colspan='3'>Prior to Arrival</td></tr> </table>"; } } SELECT DATE_FORMAT(date_submitted, '%m/%d/%Y') AS date_submitted FROM guestquestionnaire // Checks if the submitted is a number. If so, isolates the ID and adds "where" clause $id = (!empty($_GET['ID']) && is_numeric($_GET['ID']))? " where ID = '".$_GET['ID']."'" : ""; // Add the $id to the end of the string // A single call would be SELECT * FROM guestquestionnaire where ID = '1' $query = $handler->query("SELECT * FROM guestquestionnaire{$id}"); $query->setFetchMode(PDO::FETCH_CLASS, 'guestquestionnaireEntry'); while($r = $query->fetch()) { echo $r->entry, '<br>'; } ?>

Improve this question
3
  • a blank page hints more towards a php syntax, or other error, not your query. Commented Jul 26, 2015 at 6:58
  • do you store the date in proper format in database. i.e. is the date column data type is date? Commented Jul 26, 2015 at 6:58
  • The type is 'datetime' in the database Commented Jul 26, 2015 at 6:59

4 Answers 4

Reset to default
1

The function you're looking for is date_format:

SELECT DATE_FORMAT(date_submitted, '%m/%d/%Y') AS date_submitted FROM guestquestionnaire
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you. It still doesn't seem to go through though; still a blank page.
0

select convert(varchar(),YourDateColumn,103) from YourTable

Improve this answer

Comments

0

try:

SELECT date_format(`date_submitted`,'%m/%d/%Y') as `date_submitted` FROM `guestquestionnaire`

Update:

Now that the full code has been posted it appears the problems did not lie with the sql. I have not tested the following but it looks more or less ok so I hope it works.

<?php try { $handler = new PDO('mysql:host=localhost;dbname=***', '***', '***'); $handler->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); } catch( PDOException $e ) { die( $e->getMessage() ); } class guestquestionnaireEntry { public $id, $date_submitted,$entry; public function render(){ return " <tr style='font-size: 8pt;'> <td width='60%'> <a href=\"?id={$this->id}\">ID</a> </td> <td width='40%' colspan='2'>{$this->date_submitted}</td> </tr> <table border='1' align='center'> <tr style='background: #566890; font-size: 8pt; font-weight: bold; color: #fff;'> <td colspan='3'>Prior to Arrival</td> </tr> </table>"; } } $id = !empty( $_GET['ID'] ) && is_numeric( $_GET['ID'] ) ? "where `ID`='".$_GET['ID']."'" : ""; $sql = trim("select * from `guestquestionnaire` {$id}"); $query = $handler->query( $sql ); $result = $query->fetchAll( PDO::FETCH_CLASS, 'guestquestionnaireEntry' ); foreach( $result as $obj ) echo $obj->render(); ?>
Improve this answer

7 Comments

Thank you. It still doesn't seem to go through though; still a blank page.
If it is not the sql at fault then you would need to post more code so that others can deduce where the faults lie
@CodingBoy Boy any idea what might be causing the blank page?
if i am understanding your question ,you want is to retrive date and time which is stored in date_sumitted column in database..??
Correct. It already retrieves the date and time, I just want to change the format that it comes through (i.e. not YYYY-MM-DD)
|
0

sorry for my bad english..

if i am understanding your question ,you want is to retrive date and time which is in DateTime format and stored in date_sumitted column in database..

try below code once .. works for me. i hope It will be helpful to you

$stmt = $db->query("SELECT DATE_FORMAT(date_submitted, '%m/%d/%Y') AS date_submitted FROM guestquestionnaire "); $row = $stmt->fetchall(PDO::FETCH_ASSOC); foreach ($row as $key) { $date = $key['date_submitted']; echo $date; }
Improve this answer

3 Comments

Thank you, that doesn't seem to work though. It just gives me blank results (i.e. nothing there).
Yes. Both give me blank data.
hmm..it means i failed to help you..Actually my codes are giving the expect results that's why i post answer..

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.