1

MySQL stores dates in a yyyy-mm-dd numerical format.

I need the dates to be recalled in the fallowing format %d %b %Y i.e 6 Sep 2016

What I trying to think about how to handle is how to make sure if a date is entered that it post correctly in the right format to MySQL

so if the data is entered in any format it is recorded correct in SQL database.

For example US users would default enter dates 9/6/2016 even though our format is supposed to be 9 Sep 2016. MySQL seems not to handle numerical variations as well as I had hoped.

What's the best/efficient way to handle this short up using a calendar picker tool?

haightc
  • 11
  • 3
  • 1
    Look into using [STR_TO_DATE](https://www.techonthenet.com/mysql/functions/str_to_date.php). The source of the date input (e.g. a datepicker) has nothing to do with thd actual MySQL problem. – Tim Biegeleisen Sep 09 '16 at 00:52
  • This answer might be useful: http://stackoverflow.com/questions/15327726/how-to-change-date-format-according-to-the-country – kdvy Sep 09 '16 at 01:00

2 Answers2

0

You can convert PHP to MySQL like so:

$date = date('Y-m-d', strtotime($date));

You can convert MySQL to your preferred format like so:

$date = date('Y F d', strtotime($date));

Source: http://php.net/manual/en/function.date.php

grepsedawk
  • 3,324
  • 2
  • 26
  • 49
  • I have been using this method, but I was wondering about ideas to be able to translate the most common date entry formats to MySQL correctly. Right now dates are basic text date type input field I haven't tried chrome before but I found out that chrome tells you to enter a date in mm-dd-yyyyy format. – haightc Sep 09 '16 at 15:06
  • This is the exact method I use. – grepsedawk Sep 09 '16 at 15:07
-3

Store date as database require format. While you want to show date in your format. You can use php function strtotime()

 $new_format_date = date('d-m-Y',strtotime($date));
ThaTal
  • 11
  • 4
  • Incorrect usage of `strtotime`. The function expects to be given a string containing a datetime (`$time`) and will try to parse that format into a Unix timestamp, relative to the timestamp given in `$now`, or the current time if `$now` is not supplied: `int strtotime ( string $time [, int $now = time() ] )` – Zoli Szabó Sep 12 '16 at 18:36
  • Can you please share your code so i will try to help you. – ThaTal Sep 29 '16 at 06:19