[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: Date Problem
From:       "Roger Baklund" <roger () charlott ! no>
Date:       2003-08-08 17:05:30
[Download RAW message or body]

* Girish Agarwal
> Hi All,
>        I have added the date column ( column name is
> dolv ) in mysql as CHAR(10)

Then it is not a 'date column', but s string column... ;)

See the manual for a description of the different column types:

<URL: http://www.mysql.com/doc/en/Column_types.html >
<URL: http://www.mysql.com/doc/en/Date_and_time_types.html >
<URL: http://www.mysql.com/doc/en/DATETIME.html >

There are many special functions for use with date & time type columns:

<URL: http://www.mysql.com/doc/en/Date_and_time_functions.html >

> as I needed the date in
> mmddyyyy separated by mm/dd/yyyy.

The formatting of the date column should be done when you query the table:

SELECT *,DATE_FORMAT(dolv,"%m/%d/%Y") AS dolv
  FROM table
  WHERE ...

> Now I want to have
> this column contain the date whenever the updation to
> that column is done.

Then you could use the TIMESTAMP coulmn type, which does exactly this, if
you meant "... whenever the updation to that ROW is done.". If you use a
DATETIME column type, you could use the NOW() function:

UPDATE table SET
  some_column = 'new value',
  other_column = 'other value',
  dolv = NOW();

The TIMESTAMP column does this automatically.

> Please advise as to how I can
> achieve this without losing the data I have already
> entered.

To convert your existing rows to proper dates:

# make a backup of the entire table
CREATE TABLE backup_table SELECT * FROM table

# create a new column:
ALTER TABLE table ADD dolvdate TIMESTAMP;

(Use DATETIME or DATE instead, if you don't wan't the TIMESTAMP "magic".)

Now we populate the new dolvdate column with a date created by substrings
from the dolv column. We must split the "mmddyyy" or "mm/dd/yyyy" strings
into "mm", "dd", and "yyyy", so that we can feed them to mysql as
yyyy-mm-dd.

# Fix dates in "mm/dd/yyyy" format:
UPDATE table SET dolvdate = CONCAT(
  MID(dolv,7,4),'-',MID(dolv,1,2),'-',MID(dolv,4,2));

# Fix remaining dates in "mmddyyyy" format:
UPDATE table SET dolvdate = CONCAT(
  MID(dolv,5,4),'-',MID(dolv,1,2),'-',mid(MID,3,2))
  WHERE dolvdate = '0';

# check that everything is converted:
SELECT COUNT(*) FROM table
  WHERE dolvdate = '0';

# if something is not converted, check what it is:
SELECT * FROM table WHERE dolvdate = '0' LIMIT 10;

# remove old column
ALTER TABLE table DROP dolv;

# rename new column
ALTER TABLE table CHANGE dolvdate dolv TIMESTAMP;

HTH,

--
Roger


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=mysql@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]