mysql formatting a date

Question

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

Answer 1

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;

Answer 2

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.

Answer 3

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');