0

Im trying to query some data and one of them is a datetime format. I want that is shows dd/mm/yy with no time on it directly form the select. Is this possible?

My query is and the join_date is datetime that i need to be changed to short date:

$query = mysql_query("SELECT id,username,join_date,is_active FROM members",$con) 
or trigger_error(mysql_error());

This query goes directy in a Json output array. So i want to convert it directy form the query.

Ganesh Rengarajan
  • 2,006
  • 13
  • 26
Wainman Sudamerica
  • 23
  • 3
  • possible duplicate of [How to cast DATETIME as a DATE in mysql?](http://stackoverflow.com/questions/1468807/how-to-cast-datetime-as-a-date-in-mysql) – Alex K. Jul 23 '13 at 14:37
  • It's not a duplicate. It's not about simply casting as date, it's about the date format. – fancyPants Jul 23 '13 at 14:40

3 Answers3

2

Use the MySQL DATE_FORMAT function for this:

SELECT id, username, DATE_FORMAT(join_date, '%d/%m/%y') AS join_formatted, is_active
FROM members

In this example, the column name for the formatted date will be join_formatted, and its type will be VARCHAR.

The format string returns the date as dd/mm/yy as requested, but I'm personally more comfortable when the date includes the full century. To get the full century, use uppercase Y in the format string: %d/%m/%Y.

Ed Gibbs
  • 25,924
  • 4
  • 46
  • 69
  • +1. for assigning an alias for the expression, +1 for giving a description of the approach used, +1 for omitting the code that doesn't need to change, and showing just the SQL text required, +1 for giving a SQL statement that can be tested. This is a good answer. (You might also note that the value returned is not a DATE but is a VARCHAR). – spencer7593 Jul 23 '13 at 14:49
  • Thanks for the kind feedback @spencer7593! Your suggestion to note the return type is, as usual, spot on. I've added it to my answer. – Ed Gibbs Jul 23 '13 at 15:00
  • @Ed: The answer you provided is the kind of answer that I'm looking for, when I'm searching for an answer on StackOverflow. – spencer7593 Jul 23 '13 at 15:19
0

try this :

"SELECT id, username, DATE_FORMAT(join_date,'%d/%m/%y'), is_active FROM members"
Daniel Robertus
  • 1,100
  • 1
  • 11
  • 24
  • yep it sould be little `y` read more on http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format – Daniel Robertus Jul 23 '13 at 14:41
0

Use DATE_FORMAT:

SELECT id, username, DATE_FORMAT(join_date, "%d/%m/%y") AS date FROM members;

%Y Year, numeric, four digits
%y Year, numeric (two digits)

For details about date format link

Naveen Kumar Alone
  • 7,536
  • 5
  • 36
  • 57