mysql formatting a date

Hello

I am trying this query:

SELECT ARTICLE_NO, 
    USERNAME, 
    ACCESSSTARTS, 
    ARTICLE_NAME, 
    date_format( ACCESSSTARTS, '%d %m %Y' ) AS shortDate 
FROM AUCTIONS 
WHERE upper( ARTICLE_NAME ) LIKE '%hardy%' 
LIMIT 0 , 10;

Which works fine, but shortDate is null, and I am unsure why.

The contents of the database is like so:

ARTICLE_NO    USERNAME     ACCESSSTARTS          ARTICLE_NAME  shortDate
110313660559  banger-wear  17.11.2008 13:24:56   acolshirt     NULL

edit: The accessstarts field is not a datetime field but a varchar. I am unsure of what to do. Should I simply strip everything after the first space in ACCESSSTARTS to display only the date?

Or would it be better to convert the column to a datetime field, and if so, how would I specify that it should be in %D.%M.%Y format instead of the default, which apprantly starts with %Y

From stackoverflow

• The formatting of ACCESSSTARTS looks like the date you have there is a varchar, but DATE_FORMAT expects a DATE or DATETIME value.

  YOu could try using STR_TO_DATE to turn that string into a date value first, e.g.

  SELECT 
     ACCESSSTARTS, 
     date_format(str_to_date(ACCESSSTARTS, '%d.%m.%Y %k:%i:%s'), '%d %m %Y' ) AS shortDate
  FROM AUCTIONS 
  WHERE upper( ARTICLE_NAME ) LIKE '%hardy%' 
  LIMIT 0 , 10;
  

  Joshxtothe4 : yes, bingo. How could you tell it was a varchar? Because of the different delimiters?

  Paul Dixon : MySQL would typically format a date in ISO8601 style, e.g. 2008-11-17 13:24:56

• 17.11.2008 13:24:56 doesnt seem a valid mysql date. Are you storing that column as a Date and if so, is there any formatting thats applied to that date. You could use a combination of STR_TO_DATE to convert the string format to a date and then use DATE_FORMAT on the date to achieve the result.

• ACCESSSTARTS looks like a string type of column in stead of a datetime. Try replacing your

  date_format( ACCESSSTARTS, '%d %m %Y' )
  

  by this:

  date_format(str_to_date(ACCESSSTARTS, '%d.%m.%Y %H:%i:%S'), '%d %m %Y');