0

I code in the SQL, but I want to change the date format from default of My SQL to different format "DD/MM/YYYY" to use this format to code, but I recieved an error.

More about version of SQL:

SQL: My SQL Workbench 8.0.30 build 2054668

Window 11 Pro

Language: English

So, how can I change the date format or what is the true type of "dmy"? Please help me.

This is code that I used: set dateformat dmy

And error is: "Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'dmy' at line 1"

Dương Đại Sơn
  • 1
  • 4
    `set dateformat` is for SQL Server, not MySQL, and you shouldn't use it there, either. Even when you do, this misunderstands what is happening. The date values themselves are not stored as human-readable strings at all, but instead stored as binary values that are optimized for storage and indexing. – Joel Coehoorn Nov 05 '22 at 02:31
  • 4
    Also, telling us the version of MySQL is "MySQL Workbench 8" is like telling us the car you drive is a "Ford Sony", because Sony is the brand name you see on the dash radio. Fine for a normal person, but alarming from your mechanic. Workbench is the just the tool for connecting to the database, and doesn't necessarily have any relationship to the version used in the server itself. – Joel Coehoorn Nov 05 '22 at 02:32
  • Does this answer your question? [How to change MySQL date format for database?](https://stackoverflow.com/questions/4052862/how-to-change-mysql-date-format-for-database) – kmoser Nov 05 '22 at 03:32
  • Is it correct that you are just not satisfied with the format the My SQL Workbench chooses to show you dates, and you want to change this setting, so that My SQL Workbench uses your prefered format instead when you select dates with this tool? – Thorsten Kettner Nov 05 '22 at 08:38

1 Answers1

0

At the moment, we don't really know the reason why you want to change the date format, but I have a couple of assumption:

  1. You want to insert date data from front end into the table but couldn't do so due to the mismatched date format.
  2. You want the end output to show the date format of DD/MM/YYYY instead.

Although, it may look like you need to change the table column date format, there's a way to avoid that operation entirely. However, since you've mentioned changing from YYYY/MM/DD in your title, I'm not sure if your date column is actually DATE datatype because the default MySQL should be YYYY-MM-DD. Nonetheless, I'll address the matter in this answer altogether.

Modify the date format in query and leave the table date column datatype as it is:

From DD\MM\YYYY to MySQL DATE datatype format YYYY-MM-DD

... STR_TO_DATE(data, '%d/%m/%Y')

.. from YYYY-MM-DD to DD/MM/YYYY

... DATE_FORMAT(data, '%d/%m/%Y')

You can use any of that anywhere in a query; whether in SELECT or WHERE.

If "in query" is not what you want and you still want to update the table:

Well, you have two options here:

  1. Directly modify the column datatype then update the value:
ALTER TABLE mytable MODIFY COLUMN date_col VARCHAR(255);
UPDATE mytable SET date_col =DATE_FORMAT(date_col , '%d/%m/%Y');
  1. Or you can add another column, populate the desired date format there and keep the original date column as it is:
ALTER TABLE mytable ADD COLUMN my_date VARCHAR(255);
UPDATE mytable SET my_date =DATE_FORMAT(date_col , '%d/%m/%Y');

this way you have the option to directly use MySQL date functions on the default MySQL date column without the hassle of converting your desired date format into the default before you can use date functions. What I'm saying is something like this:

DAY(mysql_default_dateformat) 
is similar to
DAY(STR_TO_DATE(your_dateformat, '%d/%m/%Y'))

which means that you can use DAY() (date function) on the default date format directly without the need to convert what is not default first.

Here's demo fiddle examples

FanoFN
  • 6,815
  • 2
  • 13
  • 33