Page 1 of 1
Re-format a date coming into MySQL without PHP
Posted: Mon Jun 09, 2003 12:05 pm
by Crashin
Hey all. I'm inserting dates into MySQL from FileMaker Pro (an easy-to-use, MS Access-like "DB"). The problem is FileMaker stores dates in the MM-DD-YYYY format and
will not store them any other way. So, when I try to insert them into MySQL they obviously won't go in.
Is there a way to re-format the date within the SQL? I know conversions of dates can be made coming out of MySQL via DATE_FORMAT(), however I can't find anything for the reverse.
I know, I know...use something other than FileMaker. But, my hands are tied on the issue. I hate FM.
Thanks!

Posted: Mon Jun 09, 2003 12:25 pm
by SBukoski
Have you looked over the Date and Time functions for MySQL. You may be able to use one or a combination of them to achieve your goal. May not be pretty, but that's not what you're shooting for anyways.
http://www.mysql.com/doc/en/Date_and_ti ... tions.html
Posted: Mon Jun 09, 2003 12:33 pm
by Crashin
Hey, thanks for the reply. Yeah, I've looked through the MySQL site as well as some hard-copy documentation that I have, but haven't found a solution. Like I said, coming
out of MySQL in a different format is a piece of cake. But, changing the format going in is the stickler.
Maybe I'm being dense, but I'm not seeing many transforms on incoming data.
Ugh...I hate FileMaker.

Posted: Mon Jun 09, 2003 12:47 pm
by SBukoski
Can I ask what type you're storing it as on the MySQL side? Timestamp? Varchar?
Posted: Mon Jun 09, 2003 1:32 pm
by Crashin
Well, I started with datetime, but I've been trying several different types through the iterations. Date, varchar, text...
In FileMaker you can't store the date and time together, which is what I need. So, I'm storing the date and time separately in FM and concatenating them as they come into MySQL. This is all being done in test tables right now, so I'm flexible at this point with the methods.
Posted: Mon Jun 09, 2003 2:13 pm
by SBukoski
Well, I'm by no means a SQL expert. But for kicks I created a table that had a column setup as varchar2(10) which I called old_date and a column defined as datetime which I called new_date.
I created a few records by putting in values such as 01-02-2003 into the old_date field. I then ran the following SQL against the table:
UPDATE a_test
SET new_date=(concat(substring(old_date,7,4),'-',substring(old_date,1,2),'-',substring(old_date,4,2)))
This set the new_date column which is in a datetime format to the correct values (ie. 2003-01-02). Obviously you would add more to the concat function to account for the time fields.
This may not be exactly what you were looking for, but will hopefully guide you in the right direction. Should just be a matter of getting the individual pieces and plugging them in to the proper format.
Posted: Mon Jun 09, 2003 2:22 pm
by Crashin
SUBSTRING!!! SUBSTRING!!! SUBSTRING!!! Thank you! Why didn't I think of that??? I'm sure that'll do it...I'll post success in here when I get it worked out. Thanks for the tip!
Have a good day!