www.webdeveloper.com
Results 1 to 15 of 15

Thread: Can't quite figure out DATE_FORMAT()

  1. #1
    Join Date
    Feb 2006
    Posts
    27

    Can't quite figure out DATE_FORMAT()

    Hi,

    Got a problem with date_format() , using this query:

    Code:
    SELECT name, description, pic, download, DATE_FORMAT(date_added,'%D %M %Y'), dl_num FROM database ORDER BY download DESC
    Where date_added is database value. I obviously want to format date as i select it. What am i doing wrong? Looked in the manual and this looked to be the way but my syntax is wrong somewhere. Do i have to create a separate select statement for date format or something? Would it be better to format the date after the date data has been selected?

    cheers

  2. #2
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    what is the data type for the "date_added" column. it should be a date or datetime type.
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  3. #3
    Join Date
    Feb 2006
    Posts
    27
    yes, its date. I think i might have to use 'as' somewhere - as Date at the end maybe? i'll try that. I've looked through a number of ebooks on sql and there is surprisingly little about the date_format() function. Am I missing a simpler way to set the date to the correct british format of day, month, year ?

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,148
    I don't see anything obviously wrong with the query. What are the symptoms that tell you it's not working (an error message, no rows returned, etc.)?
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  5. #5
    Join Date
    Feb 2006
    Posts
    27
    There is no error given, it 'works', but the date output as expressed in date_format is ignored - the date output is still the default YYYY MM DD when results are displayed. I'm using PHP to display the results btw

  6. #6
    Join Date
    Feb 2006
    Posts
    27
    Oh, and also using echo command to display data, if this is relevant

  7. #7
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    what happens if you just echo the SQL? does it show the command properly?
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  8. #8
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,148
    I don't know if it does/should make a difference, but I always do
    Code:
    DATE_FORMAT(date_col, 'format string') AS date_formatted
    , then reference the 'date_formatted' element of each result row.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  9. #9
    Join Date
    Feb 2006
    Posts
    27
    Would I need to create a new date_formatted column in the table then?

  10. #10
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,148
    Quote Originally Posted by mcpalmer
    Would I need to create a new date_formatted column in the table then?
    No. It just means that any further reference to that formatted output within the query or, proably more importantly, within the script where you're running the query would be by that name. For instance, in a PHP script where you might be referring to that column in a result row, you would refer to it as $row['date_formatted'] instead of by the column name in the table.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  11. #11
    Join Date
    Feb 2006
    Posts
    27
    Nope, it doesn't work, i reference $row['date_formatted'] and the table column for the data is blank. Could there be a problem with my php or sql settings?

  12. #12
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,148
    Quote Originally Posted by mcpalmer
    Nope, it doesn't work, i reference $row['date_formatted'] and the table column for the data is blank. Could there be a problem with my php or sql settings?
    Yes.

    Show us the PHP if you want.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  13. #13
    Join Date
    Feb 2006
    Posts
    27
    Here it is:

    Code:
    mysql_select_db($database_ss_list, $ss_list);
    
    if(!reorder){
    $query_Recordset1 = "SELECT name, description, pic, download, DATE_FORMAT(date_added,'%D %M %Y') AS date_formatted, dl_num FROM screensavers ORDER BY download DESC";
    
    } else {
    	$query_Recordset1 = "SELECT name, description, pic, download, date_added, dl_num FROM screensavers ORDER BY '$reorder' DESC";
    }
    
    $unformatted_date = $row_Recordset1['date_added'];
    $Recordset1 = mysql_query($query_Recordset1, $ss_list) or die(mysql_error());
    $row_Recordset1 = mysql_fetch_assoc($Recordset1);
    $totalRows_Recordset1 = mysql_num_rows($Recordset1);
    ?>
    and then i try to call data in the html:

    Code:
    <?php echo $row_Recordset1['date_formatted']; ?>
    all other calls to query data work fine, as does date_added when i call it. The only problem is the date_formatted data above. Any ideas?

  14. #14
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    if date_added works, but date_formatted isn't, I'm thinking it's more to do with your logic. date_added should not work in the if(!reorder) part of the condition. You might want to check the logic within the page to see which sql is getting processed.
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  15. #15
    Join Date
    Feb 2006
    Posts
    27
    Of course, you are right! Left the dollar sign off $reorder variable so it was automatically running the else code, cheers it works now, thanks for the help

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles