-2

My date field currently saves data in the following format:

2016-09-02 14:04:43

Instead, I'd like it to say:

02 September 2016, 2:04 PM

Is there an easy way to convert it using PHP/MySQL statements?

EDIT: I would like to also be able to select let's say the rows where the date is in the last 30 days. For example, select all rows where date is > 2017/03/06

GRS
  • 2,807
  • 4
  • 34
  • 72
  • You should make the column DATETIME and not worry about how it's stored. You just need to worry about how it's *displayed*, which you can deal with in your SELECT. – Ken White Apr 05 '17 at 23:58
  • I have a column called DATE, and it's stored like above, when I echo it, it's displayed exactly like it's stored – GRS Apr 05 '17 at 23:59
  • I said **type**, not **named**. Make the column DATATYPE a DATETIME, not a CHAR() or VARCHAR(), and it's stored internally in a pre-set format that you don't need to worry about. – Ken White Apr 06 '17 at 00:00
  • I see, it's now datetime but it's still echoing non-formatted type. Will I need to add CONVERT(VARCHAR(19),GETDATE()) sort of thing at the end of my $sql query? – GRS Apr 06 '17 at 00:06
  • Where's the PHP code where you are trying to echo it? – chris85 Apr 06 '17 at 00:06
  • In the wordpress setting, I find the user by $user->wp_get_current_user(); echo $user->date_registered; – GRS Apr 06 '17 at 00:08
  • You format it when displaying (echoing) it to look like whatever you want. – Ken White Apr 06 '17 at 00:08
  • Do I need to do http://php.net/manual/en/class.datetime.php ? – GRS Apr 06 '17 at 00:09
  • Ok thanks, I think I need to use date() function – GRS Apr 06 '17 at 00:11

1 Answers1

0

You can use the MySQL function: date_format(). In your case, you can try:

DATE_FORMAT(NOW(),'%d %M %Y %h:%i %p)

more about it here.

Also do the change people have told you, make the column datetime.

Disturb
  • 558
  • 8
  • 14