MySQL / PHP gurus help with this code?
 

I have tried a couple help forums and #php but no luck so far.

I have a varchar field (date) with a date in MM-DD-YYYY format. I need to convert this to my (date2) field which is DATE in the YYYY-MM-DD format. Here is the sql statement I have so far - and it doesn't work:

UPDATE b1datefix t1, b1datefix t2 SET t1.date2 = DATE_FORMAT(t1.date,'%Y-%m-%d') WHERE t1.id=t2.id;

Any ideas?

i dont work with mysql, but i'd think it would have some sort of globalization stuff built in so that it can natively accept the mm-dd-yyyy date format?

The problem is that half of my DB is one format and half is the other. If sponsors used the same damn date format I wouldn't have this problem. So I am selecting queries based on a certain format and I don't want to run checks at the php level before running queries it just slows the process down.

If you were to do it in PHP check out the strtotime() function, get the timestamp, then use date() to get it back into the format you need. Not the cleanest but it would work.

You'll have to do it from the application.
The date_format and other functions expect a date in the default format %Y-%m-%d

Thanks, that's what I was afraid of.

Try something like this (I'm not awake yet so might have a typo in there heh)

UPDATE b1datefix SET date2 = CONCAT(SUBSTRING(date,-4),'-',SUBSTRING(date,1,2),'-',SUBSTRING(date,FROM -7 FOR 2));

Enjoy :)

Keith I think that would work, only problem is I am getting a syntax error and this is too advanced for me to troubleshoot:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM -7 FOR 2))' at line 1

Aha I knew I'd have a typo :1orglaugh - Damn comma.

Try this:

UPDATE b1datefix SET date2 = CONCAT(SUBSTRING(date,-4),'-',SUBSTRING(date,1,2),'-',SUBSTRING(date FROM -7 FOR 2));

hey there

he wrote that he wasn't fully awake ;)

there is some garbage in the sql, (FROM and FOR).. the sql should read:

UPDATE b1datefix SET date2 = CONCAT(SUBSTRING(date,-4),'-',SUBSTRING(date,1,2),'-',SUBSTRING(date, -7,2));

let us know how it goes ;)

peace

Not sure about that "not the cleanest" :) That method is actually quite common and is a good choice, IMO. :thumbsup to RichCashMike

Thanks, though FROM FOR works as well just I had a comma I shouldn't have :)

I ran the command and it affected all of the rows but it didn't pull in the date. All of the dates came in as: 0000-00-00
So something is wonky and I can't figure it out.

SELECT CONCAT(SUBSTRING(date,-4),'-',SUBSTRING(date,1,2),'-',SUBSTRING(date FROM -7 FOR 2)) from b1datefix limit 10;

run that to see what comes out.

Keith,
10 rows of

-02-

What version of mysql are you using ? I don't see why the first/last substrings wouldn't work unless it's a version thing.

I made a test case for oyu actually and on my server it works perfectly:

mysql> desc datefix;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| date1 | varchar(10) | YES | | NULL | |
| date2 | date | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from datefix;
+------------+-------+
| date1 | date2 |
+------------+-------+
| 03-23-2007 | NULL |
+------------+-------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT(SUBSTRING(date1,-4),'-',SUBSTRING(date1,1,2),'-',SUBSTRING(date1 FROM -7 FOR 2)) from datefix;
+-----------------------------------------------------------------------------------------+
| CONCAT(SUBSTRING(date1,-4),'-',SUBSTRING(date1,1,2),'-',SUBSTRING(date1 FROM -7 FOR 2)) |
+-----------------------------------------------------------------------------------------+
| 2007-03-23 |
+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Or avoid mysql's functions, and just get the data then update the data

Or just work with unix timestamps in the DB for all date/time fields - that'smy preference. :)

However I think for Brad's purpose here doing it in mysql would be preferred instead of in the code...he could even use mysql's schedule feature to have it run every 5 mins or whatever he wishes, so no need to alter any code anywhere or even setup any cronjobs.

MySQL 4.0.26-standard

That's probably why there is a problem. :/

Yah both ways work, would really depend on how often it needs to be done, if the source of the input could be switched/fixed.

I don't have any version 4.x around anymore so can't help you much there...I would say move to 5.1 the are a LOT of new features to like plus upgrading is like a 5-10 minute job if you know how to do it.

Otherwise, yeah you are probably stuck doing it on the php end.

Last try that might work for you since the middle one did work form your output:

CONCAT(SUBSTRING(date,7,4),'-',SUBSTRING(date,1,2),'-',SUBSTRING(date,4,2))

OMFG you beautiful man that worked. Holy shitolskies!!!

Thanks guys I appreciate the help you just taught me a lot of new info. HUGSSSZZ!!

Not sure why I didn't just use this option first instead of the fancier way :1orglaugh

Guess this means I'm finally awake now and should head in to the office...later :pimp

Or you should take the day off and relax because your brain's "thinking" in the "non-fancy" way.

I would if I had an epiphany like that.

BTW - fun thread to watch... thanks geeks. :)