Please help stupid me with date prob

[installer69]

I have a 15,000 record mysql database but when I created it I set the date field to Varchar (I know, I know) My problem is the format that they have been stored in is dd/mm/yyyy. When I change the field properties to date it sets them to the year 2030!!
Please help.

[leatherback]

Ehh..

How about adding a colum to your database, querying the database for the stored date, and inserting the date (In proper date formatted) in the newly made colum?
It will take some time to run, but it should work. Afterwards you can delete the old date colum, nd rename the new colum.

???

J.

[installer69]

dank u vel J.

I'll get my guide out and give it a go. The thing is that we always use the dd/mm/yyyy format over here. I've learnt php/mysql through necessity and only learnt the things I needed at the time so never aquired a full understanding.

Craig

[xblue]

you actually can do the update to a proper format in one query, I put this together once for someone else:

http://www.phpbuilder.com/board/showthread.php?s=&threadid=10229716#post10300692

[installer69]

Thanks xblue. I knew (hoped) I couldn't have been the only foolish person to ever to do this when just starting out. Perhaps you could advise me further. I use twang to convert an excel sheet into a dump file. The date on the excel sheet is stored in the format dd/mm/yyyy. How should this be converted so that mysql understands it when it is inserted.

Thanks,

Craig

[installer69]

Hey xblue,

don't reply until I've read your previous post fully because it looks like you may have answered all/most of my questions. I'll repost if I get stuck to see if you can help. Thanks again.

[installer69]

OK, the first part

STEP 1: convert the old MM/DD/YYYY data
instead of the steps outlined above, you can as well do it in one statement (I had to test this first)
UPDATE table_name SET date_column = CONCAT(SUBSTRING(date_column, 7, 4), SUBSTRING(date_column, 1, 2), SUBSTRING(date_column, 4, 2))
(if your old format is different from MM/DD/YYYY, change the numbers passed to SUBSTR if necessary - first one is start position, second is length.)

worked great - it converted my 'jobdate' column to yyyymmdd but when I ran the second query:

STEP 2: change the column type
ALTER TABLE table_name CHANGE date_column date_column DATE

it set all values to 0000-00-00

My original properties for 'jobdate' were varchar 10 not null.

Help please

[xblue]

umm, I tested this when I wrote it down, it seemed to work out fine for me.

I'll check again, may take a while until I'm back.

[xblue]

no, I tested it again, it is still working fine for me.

old field type: VARCHAR(10)
old format: DD/MM/YYYY

some example data:
31/01/2001
01/02/2002

after UPDATE query:
20010131
20020201

after ALTER query:
2001-01-31
2002-02-01

only the records that contained invalid data, like strings or misformatted dates are set back to 0000-00-00.

[installer69]

Ok I think I have it. Your post said:

STEP 1: convert the old MM/DD/YYYY data
instead of the steps outlined above, you can as well do it in one statement (I had to test this first)
UPDATE table_name SET date_column = CONCAT(SUBSTRING(date_column, 7, 4), SUBSTRING(date_column, 1, 2), SUBSTRING(date_column, 4, 2))
(if your old format is different from MM/DD/YYYY, change the numbers passed to SUBSTR if necessary - first one is start position, second is length.)

but my data is DD/MM/YYYY not MM/DD/YYYY so when converted mysql doesnt understand for example the 3rd day of the 29th month!! i think I have it now, just change the numbers around a bit. I'll repost to tell you how good you are and how well I got on.
Thanks again.

[installer69]

Ok - this worked for me...

UPDATE installsheet SET jobdate = CONCAT(SUBSTRING(jobdate, 7, 4), SUBSTRING(jobdate, 4, 2), SUBSTRING(jobdate, 1, 2))

Surely there can't be many things in life that you struggle to understand complicated strings of code, work late into the night, try, try and try it out again then get foiled at the last step by a missing ; or )