Date format

[elite311]

I have a column in my database storing date as DATE (shopdate is the field name) and I want to format the output from YYYY-MM-DD to Jan 12 2012 can someone help me figure out what Im doing wrong on my query?

 

$result = $db->fetch_all_array("SELECT * FROM workshop WHERE shopdate >= CURDATE() DATE_FORMAT(shopdate,'%b %D, %Y') ORDER BY shopdate LIMIT 1");

 

The query works fine as long as I remove

DATE_FORMAT(shopdate,'%b %D, %Y')

 

 

My output look like this:

 

<table width="100%" cellspacing="5">
    <?php foreach($result as $option2) { ?>
     <tr>
       <td align="right" valign="top"><div class="shopdate">Date:</div></td>
       <td align="left" valign="top"><div class="shopdatedesc"><?php echo $option2["shopdate"];?></div></td>
     </tr>
     <tr>
        <td align="right" valign="top"><div class="shoptype">Workshop:</div></td>
        <td align="left" valign="top"><div class="shoptypedesc"><?php echo $option2["shoptype"];?></div></td>
     </tr>
     <tr>
       <td align="right" valign="top"><div class="shoptime">Time:</div></td>
       <td align="left" valign="top"><div class="shoptimedesc"><?php echo $option2["shoptime"];?></div></td>
     </tr>
    <?php } ?>
</table>

 

I've been doing some reading on date format but I can't figure this out.

[gin]

You syntax is wrong.

 

SELECT DATE_FORMAT(shopdate,'%b %D, %Y'), shoptype, shoptime FROM workshop WHERE shopdate >= CURDATE() ORDER BY shopdate LIMIT 1

[elite311]

Interesting, so I can't just select all and than format the date?

 

Your query worked to show 'shoptype' and 'shoptime' however the 'shopdate' doesnt show on the output, any idea why?

[gin]

No, I think you're thinking of one of the other languages where the formatting comes at the end. For MySQL, the formatting happens when you call the field.

 

Ah, my mistake, here it is:

SELECT DATE_FORMAT(shopdate,'%b %D, %Y') AS shopdate, shoptype, shoptime FROM workshop WHERE shopdate >= CURDATE() ORDER BY shopdate LIMIT 1

 

In my first response, the query would have returned a field called "DATE_FORMAT(shopdate,'%b %D, %Y')" along with "shoptype" and "shoptime".

[elite311]

Awesome thanks! worked perfectly.

 

Also thanks for pointing out that you cant select all than format, I' sure that will save me when making other querys

[elite311]

I just noticed a problem, now the date order is not correct because it's ordering the dates by the names (jan, feb, mar...) instead of the actual date. i thought formatting the date would just change how it's outputted not how its sorted because the date is still stored as YYYY-MM-DD in the database

[elite311]

Not sure if this is correct but it seems to work I changed the name after the AS to something different and then changed my output to read that new name like this:

 

$result2 = $db->fetch_all_array("SELECT DATE_FORMAT(shopdate,'%b %D, %Y') AS newtag, shoptype, shoptime FROM workshop WHERE shopdate >= CURDATE() ORDER BY shopdate ASC LIMIT 1");

 

<?php echo $option2["newtag"];?>