Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2011
    Posts
    3

    Question Unanswered: need to add a DATE column in a table

    i am using mysql-v5.1 and i want to add a DATE column in a table,

    my requirement is,whenever i do insert a new row that column should update with current date and the date format should be like 'yyyymmdd' or 'yyyy-mm-dd' not like 'yyyy-mm-dd hh:mm:ss' .

    so kindly tell me how to add a such a column.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you are in luck
    Code:
    ALTER TABLE foo
    ADD COLUMN bar TIMESTAMP
    here's the explanation from da manual --
    A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation. By default, the first TIMESTAMP column in a table is automatically set to the date and time of the most recent operation if you do not assign it a value yourself. You can also set any TIMESTAMP column to the current date and time by assigning it a NULL value. Variations on automatic initialization and update properties are described in Section 10.3.1.1, “TIMESTAMP Properties”.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2011
    Posts
    3

    Thumbs down my requirement is not like this

    if i add TIMESTAMP ,it will make column as "2011-11-16 15:13:00" but i need column as "2011-11-16"(only date).

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by prabhakaran66 View Post
    if i add TIMESTAMP ,it will make column as "2011-11-16 15:13:00" but i need column as "2011-11-16"(only date).
    you can use DATE_FORMAT function to show only the date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2011
    Posts
    3
    is it possible to define that in column description.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no it isn't

    your alternative is to use a DATE column, but you will have to remember to update the value yourself every time you insert or update a row
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •