Click here to get back home

Date format
 Home | NewsGroups | Search | About
Login Password
Register Now! Lost Password?
 mailing.database.mysql    Post an article   get this group's latest topics as an RSS feed add this group's latest topics to your My MSN content add this group's latest topics to your My Yahoo content
Subject Author Date
Date format Donald Campbell 08-18-2008
|--> Re: Date format Gordon Burditt08-18-2008
Get Chitika Premium
Posted by Donald Campbell on August 18, 2008, 4:11 pm
Please log in for more thread options


I am looking at moving some old database systems to MySQL and I am
currently working on data loading using mysqlinport.

I have an issue with dates as all the data that is coming my way has
dates in DD/MM/YYYY. the load routines look to only accept it in
YYYY/MM/DD.

Is there anyway of telling the routines to use the European date format?

Would be nice if it could display dates in the format as well.

I did find a variable called "data_format" in the online documentation,
however, it said that it was no-longer used.

THanks.

Don


Posted by Gordon Burditt on August 18, 2008, 5:45 pm
Please log in for more thread options

>I am looking at moving some old database systems to MySQL and I am
>currently working on data loading using mysqlinport.
>
>I have an issue with dates as all the data that is coming my way has
>dates in DD/MM/YYYY. the load routines look to only accept it in
>YYYY/MM/DD.

My suggestion is not to try to fight MySQL in terms of how the data
is *stored*. Using date_format(), you can get the data out of the
database in one of an incredible number of formats. Using str_to_date(),
you can convert input in lots of formats to a date, provided you know
what format is coming in.

>Is there anyway of telling the routines to use the European date format?

I'm not that experienced in the use of mysqlimport, but I believe you
can import the files like this:

1. Set up your table, with string fields for the dates.
2. Do the import.
3. Add date fields to the table.
4. Fill the date fields from the corresponding string fields, using
something like
UPDATE table SET datefieldname = str_to_date(stringfieldname, '%d/%m/%Y');
Repeat if you've got more than one date field you're trying to import.
5. Drop the string fields.


Posted by Tim Streater on August 18, 2008, 5:52 pm
Please log in for more thread options



> I am looking at moving some old database systems to MySQL and I am
> currently working on data loading using mysqlinport.
>
> I have an issue with dates as all the data that is coming my way has
> dates in DD/MM/YYYY. the load routines look to only accept it in
> YYYY/MM/DD.
>
> Is there anyway of telling the routines to use the European date format?
>
> Would be nice if it could display dates in the format as well.

No, you have to write some PHP or JavaScript (or both, as I did) to
reformat it to/from internal format from/to human format. While you're
at it make it flexible, so it allows any delimiter rather than just the
slash, allows month names as well as month numbers, and makes some sort
of guess about what 2-digit years mean.

Similar ThreadsPosted
Date Format November 2, 2005, 7:17 pm
Date format February 1, 2006, 4:16 pm
How to specify Date format during LOAD DATA INFILE ? June 27, 2005, 6:52 pm
Converting VARCHAR "date" info to an actual date field August 15, 2005, 7:49 pm
convert field data to another format April 12, 2006, 5:16 pm
Urgent : DateTime format in MySQL September 11, 2006, 12:12 am
Returned mail: Data format error February 11, 2007, 3:43 am
RETURNED MAIL: DATA FORMAT ERROR March 7, 2007, 4:40 am
Excel -> MySQL ODBC data format problem February 9, 2006, 12:08 pm
help to retieve only the difference in minutes between two dates in DATETIME format December 18, 2006, 8:31 am

Our other projects:

Art Dolls, Fairies and Mermaids - Sunnyfaces.net

Roy's Linux, Programming and Search Engines messages

1-Script XML SitemapXML Sitemap