Geek asks and answers

Change date in MySql format

I have a date type column in MySql. The default MySql date format is YYYY / MM / DD. but in fact I prefer to use the DD / MM / YYYY format.

Can I change the date format of a column?

+5
source share
4 answers

No, you cannot change the default MySQL format for DATE, DATETIME, or TIMESTAMP columns.

But you can use the MySQL functions in your SQL statements to output the DATE expression as a string in a different format.

DATE_FORMAT( datecol , '%m/%d/%Y')  AS datecol

(This works fine in the SELECT list, but avoid using it in any predicates (i.e. the WHERE clause). There you will need to reference the empty column and convert the rows of your preferred format 'MM / DD / YYYY' using the STR_TO_DATE function, eg

datecol >= STR_TO_DATE('07/16/2012','%m/%d/%Y')

, , , DATE MySQL , .

+2

, , . , Oracle NLS_DATE_FORMAT, Oracle . MySQL .

(date_format datetime_format),

mysql> SHOW VARIABLES LIKE 'date%_format';
+-----------------+-------------------+
| Variable_name   | Value             |
+-----------------+-------------------+
| date_format     | %Y-%m-%d          |
| datetime_format | %Y-%m-%d %H:%i:%s |
+-----------------+-------------------+

, ( ). , . . .

: , . .

+1

echo date('d/m/Y', strtotime($date));

, UNIX- .

0

, , , .

I started thinking how I can make formatting possible. I have an idea. How about running this? I mean adding a column with a type char, and then updating that column with a MySQL trigger. And it worked! I did some research related to triggers, and finally came up with these queries:

CREATE TRIGGER timestampper BEFORE INSERT ON table
FOR EACH
ROW SET NEW.timestamp = DATE_FORMAT(NOW(), '%d/%m/%Y');

CREATE TRIGGER timestampper2 BEFORE UPDATE ON table
FOR EACH
ROW SET NEW.timestamp = DATE_FORMAT(NOW(), '%d/%m/%Y');

You cannot use TIMESTAMPeither DATEas a column type because they have their own format and are automatically updated.

So, here is your alternate timestamp or alternative to dates! Hope this helps, at least I'm glad I did it.

0
source

More articles:


All Articles