-1

I would like to create a new column in my database table with the current date as the column name, e.g. "2014-10-11 01:57:46" or like this "11 10 2014". I tried this:

  1. ALTER TABLE 'table_name' ADD 'NOW()' VARCHAR(255);

  2. ALTER TABLE 'table_name' ADD 'SELECT NOW()' VARCHAR(255);

  3. ALTER TABLE 'table_name' ADD 'DATE_FORMAT(NOW(), '%d %m %Y')' VARCHAR(255);

  4. SET @mydate:=DATE_FORMAT(NOW(), '%d %m %Y'); ALTER TABLE 'table_name' ADD @mydate VARCHAR(255);

All my attempts create a column but the name is not the current date. Could you be so kind to point me what I am doing wrong?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
jhnd74
  • 67
  • 6
  • 4
    Trying to name a column with the current date is what you're doing wrong. I can't see any way that this isn't a very bad idea. –  Oct 10 '14 at 22:01
  • 1
    that is a HORRIBLE idea. column names should NOT be numeric, and they should not contain spaces or other non-alphanumeric characters. – Marc B Oct 10 '14 at 22:04
  • Welcome to SO, @jhnd74! It's best to use code formatting for your code in the question (use toolbar's "Add Code" button). Also, it makes sense to add sql tag when asking sql questions. – Isantipov Oct 10 '14 at 22:04
  • 1
    @MikeW @MarcB , I'd like to point out that there are scenarious when using date in column name is an appropriate solution. E.g. you're building and audit framework where each table has an tableName_audit "mirror table" with the same set of columns, as the original table for storing historical values. Now when you drop a `ColumnName` column from original table, you can't drop it in the history mirror table (as you don't want to drop your history) - you'd rather rename it into `CColumnName_date_deleted` – Isantipov Oct 10 '14 at 22:08
  • @Isantipov Including a numeric component as part of a column name may have a use case, but the OP is trying to use just the date. Having to modify the schema on a regular basis generally indicates a design problem with the schema. The OP's attempted code here suggests a really poor design, but he hasn't explained why he want to do this. –  Oct 10 '14 at 22:13
  • @MikeW , everything depends on the particular scenario of course, but this one `DATE_FORMAT(NOW(), '%d %m %Y')'` sound like exactly the situation I describe. Of course, it's a bad idea if used to filter data by time instead of normally adding a simple date column. – Isantipov Oct 10 '14 at 22:16

1 Answers1

-1

I am no expert in MySql, but you're looking for a way to do dynamic sql which seems not to be supported in mysql (see this question ).

Here is a couple of ideas:

Community
  • 1
  • 1
Isantipov
  • 19,491
  • 2
  • 26
  • 41
  • 1
    Prepared statements won't help here. One can't use placeholders for MySQL identifiers like table or column names. –  Oct 10 '14 at 22:34