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.
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.
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.
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.
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.
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!