CODEWITHSUNDEEP
mysql
showkey
showkey

Reputation: 298

Is there a quick way to change the column's format?

select * from back limit 2;    
code    report_date total_operating_revenue
000002.XSHE 1989-12-31  15567
000002.XSHE 1990-12-31  23012

I want to keep the field report_date's year.
My failed try:

update back set report_date = date_format(report_date,'%Y');
select * from back limit 2;
+-------------+-------------+-------------------------+
| code        | report_date | total_operating_revenue |
+-------------+-------------+-------------------------+
| 000002.XSHE | 0000-00-00  |                    15567|
| 000002.XSHE | 0000-00-00  |                    23012|

Other try:

create table new as 
select code,date_format(report_date,'%Y') as report_date,total_operating_revenue from back;
drop table back;
rename table new TO back;

I feel it is complicated to use three mysql statements to change the field.
Is there a quick way to change the column's format,not just select it?

Upvotes: 0

Views: 42

Answers (3)

ikyuchukov
ikyuchukov

Reputation: 660

If you wish to more easily change Database schemes I would recommend using a GUI program as they usually make the process way easier.

I would recommend looking into:

MySQL Workbench - free - MySQL standard

HeidiSQL - free - my favorite as it's light and has a lot of functionality (Windows only, you need Wine for Linux)

phpMyAdmin - free (Uses PHP)

DataGrip - paid, InteliJ's DB product.

Upvotes: 0

showkey
showkey

Reputation: 298

alter table back modify column report_date char(4);

It can change value of field report_date such as 1989-12-31 into 1989.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521914

You should probably not even be considering storing the year; you already have the full date, from which the year alone can easily be derived. Just use:

SELECT
    code,
    YEAR(report_date) AS report_year,
    total_operating_revenue
FROM back;

Note: If you want to use LIMIT, you should also be using ORDER BY. I don't know your logic, so I have not used LIMIT in my answer.

Upvotes: 1

Related Questions