BradM

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?

Masterchief

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?

BradM

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.

RichCashMike

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.

__________________

darksoul

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

__________________
1337 5y54|)m1n: 157717888
BM-2cUBw4B2fgiYAfjkE7JvWaJMiUXD96n9tN
Cambooth

BradM

Thanks, that's what I was afraid of.

Varius

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

__________________
Skype variuscr - Email varius AT gmail

BradM

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

Varius

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

Try this:

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

__________________
Skype variuscr - Email varius AT gmail

Kaylum

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

__________________
-- "You can't stop ingenuity, trust me.."

InfinityWebCreations

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

Varius

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

__________________
Skype variuscr - Email varius AT gmail

BradM

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.

Varius

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.

__________________
Skype variuscr - Email varius AT gmail

BradM

Keith,
10 rows of

-02-

Varius

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)

__________________
Skype variuscr - Email varius AT gmail

EDepth

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

__________________
ICQ: 275335837

Varius

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.

__________________
Skype variuscr - Email varius AT gmail

BradM

MySQL 4.0.26-standard

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

EDepth

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.

__________________
ICQ: 275335837

Varius

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))

__________________
Skype variuscr - Email varius AT gmail

BradM

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

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

Varius

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

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

__________________
Skype variuscr - Email varius AT gmail

psili

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.

__________________
Your post count means nothing.