0

Is there a way to change the name of a column to the current date? I don't need it to dynamically update as the date changes -- just the date when the code was executed. I tried the below code but get a syntax error

ALTER TABLE table_name CHANGE old_column_name CURDATE() DATE;
Tomero
  • 183
  • 2
  • 10
  • 2
    No. But why would you want to name a column like that? If you think you need to do so, your database design is extremely flawed. This is very much an [XY problem](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). If you're adding columns for individual days, your schema is so broken that SuperGlue can't fix it. – Ken White Mar 26 '16 at 04:07
  • 1
    Don't violate the [Zero, One or Infinity Rule](http://en.wikipedia.org/wiki/Zero_one_infinity_rule) of proper [database normalization](http://en.wikipedia.org/wiki/Database_normalization). This is a super bad idea. – tadman Mar 26 '16 at 04:14
  • 1
    What format would you expect for such a column name? Perhaps "Saturday, 26th March 2016" or "2016/03/26" and what is the data type of that column? Surely not datetime. Can you imagine the confusion of a column headed 2016/03/26 that presented other dates. Or imagine the confusion of a query like this select `2016/03/26` from table_name where `2016/03/26` >= '2016-03-26'. It`s an absurd idea. – Paul Maxwell Mar 26 '16 at 04:57

2 Answers2

1

In the ALTER TABLE statement, the new column name must be supplied like any other identifier in the SQL text.

The new name for the column cannot be supplied as the return from a function, or as bind placeholder. It has to be supplied as an identifier.

That is, the SQL statement you submit to the database will need to have the new column name actually spelled out, as part of the statement:

 ALTER TABLE table_name CHANGE old_column_name new_column_name DATE

So, the short answer to your question is no, it can't be done in a single SQL statement.

Obviously, you can perform operations in separate steps, to get the current date, and to create a string containing SQL statement you want to execute. IT seems like you would also need to identify the current name of the column you want to change.

Beyond the question that was asked...

I'm having difficulty fathoming a use case where something like this would be an appropriate solution. What problem is this type of functionality attempting to solve? Why would you need the name of the column changed. Any SQL statements that reference the column will also need to be changed. Could you store this "date" as a value in a row of another table?

The only thing I can think why someone would want to do this would be a misguided attempt to specify a column name in a resultset from a SELECT * query.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
1

While it is probably a bad idea, what you are going to do - it is possible by using a prepared statement:

SET @stmt := CONCAT('ALTER TABLE table_name CHANGE old_column_name `', CURDATE(), '` DATE;');
PREPARE stmt from @stmt;
EXECUTE stmt;
Paul Spiegel
  • 30,925
  • 5
  • 44
  • 53
  • @Normajean - still [works for me](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=c606008d387c652477045ccd988f17c7) after 5 years. – Paul Spiegel Jun 25 '21 at 15:17