Date formats / other questions

[lolacherrycola]

Hello

I am sorry to post this question here as I',m a bit lost and not sure who I should ask……..

I set up a calender in Excel ( every day of the month ) and imported it into MySQL via SQL Yog ( good bit of software ).

1) The date format in Mysql is e.g. 'yyyy-mm-dd. Is there any way of being able to display it as e.g. dd/mm/yy?

2) Can you enter data direclty into a table in SQL Yog ( like you use Excel for instance )….at the moment I can't see how to do a 'table view' ( do you have to upgrade / buy the professional version for this feature? )

3) I don't know if you can help with this last one – I wanted to use Access via My SQL ODBC just to test opening a table. When I open a 'linked' table ( i.e. a table via Mysql ODBC driver ) the date field looks weird! Access displays a lot of squares like it can't recognise the characters. Does anyone know why it's not displaying a date? Access thinks the field is binary….anyway I will write to the ODBC people as well but if anyone can shed any light on this I would be grateful….

Thanks again for giving everyone the chance to play around with Mysql in an easy fashion …………………

[CalEvans]

1) http://dev.mysql.com/doc/mysql/en/date-and…-functions.html

look for DATE_FORMAT(date,format). If you are asking is there any way to change it in SQLYog? no, because SQLYog does not display dates, it displays the results of a query. MySQL is in charge of the format.

2) No, MySQL is a database engine. As such it is very difficult to do what you want to do. You CAN, with a properly built table, use F8 to enter data in but it is cumbersome. (and it should be) If you need ease of use you'll need to look at designing a front end application to allow you to edit your information.

3) Sorry, can't help you with this one. I only use database products. Access doesn't fall into that category. 😉

HTH,

=C=

[peterlaursen]

Here the date format does not look weird when read with Access through ODBC – see attached pic . (sorry it's danish!)

I just entered some data in an Excel spreadsheet, exported to a MySQL database and opened that MySQL database with Access through ODBC. Date is column “F3”

But I remember that the MySQL documentation has some remarks about the compability with Access.

Have you searched that documentation ?? There are som settings on the “advanced” tab that must be right.

Tip: Search for “MyODBC and Microsoft Access” in the searchable version of the doc's

Note especially that “Return matching rows” should be checked. You must also have recent versions of Microsoft MDAC and JET-engine. It's all in the MySQL documentation …

There is one user remarking:

“Beware of MyODBC 3.51.11 (and .10 probably too) with Access. I've noticed that after refreshing ('refresh linked tables') tables all date/datetime/timestamp fields turned into 'text' and I couldn't update any table containing such fields. Got back to 3.51.06 I used previously. No problems with this one.” – could that be your problem ? (though I can't reproduce that error on my system with Access 2000!)

What are the exact program versions that you use (MySQL, MyODBC, MS-Access) ?

[lolacherrycola]

Thanks to both of you for helping me out ….I see you have been successful (with the dates/ODBC). I wonder what's wrong…better check all my documentation in MySql.

I'm using MySQL 4.1.9 and the Mysql ODBC driver is version 3.51.11.00. It's also Access 2000.

I wanted to set up a calender in Excel starting e.g. 01/01/05 for all 365 days of the year. I then wanted to import this CSV into a Mysql table via SQL Log (which works) and then view this table in Access – but I'm seeing squares in Access in the date field instead of a text date like 01/01/05.

I'll check that thread out Peter thanks.

Sorry I'll try to explain this as clearly as I can!

I've tried setting the date field in Mysql table to different data types ( e.g. 'date' or 'txt' ) and then imported the CSV file into this field but ithe date field still looking like squares in Access over the Mysql ODBC connection!

I set the datatype to 'date' the Mysql table and after the CSV was imported the date comes out like '2005-10-05' using Sql Yog ( when what I really want is in the field '05/10/05' – so to achieve '05/10/05' I have used a 'text' data type instead of the 'date' type which works ).

[peterlaursen]

It could be a problem with the CSV

Excel and CSV (and ODBC too) 😡

Try this instead ….

1) Save your Excel file to the desktop , and OPEN IT FROM ACCESS (yes: you can open the .xls-file from Access! just – file … open .. Excel-files … your file)

2) Create an MySQL-database with Sqlyog if you don't allready have done so

3) Create a user with at least “insert”-privilege to that database also with Sqlyog

4) and create an ODBC-instance using that username for that database

Now from Access File .. export .. ODBC-database .. your ODBC-instance

That's what I just did !!

[lolacherrycola]

Thanks so much Peter. I wouldn't have known where to start trying to solve this!

I've done what you said and it's worked – I managed to import a table into Mysql via ODBC…..the only slight problem is that the data in the fields ( in Mysql) looks like e.g. '2005' instead of '01/01/2005' – it's created a 'varchar' 4 length datatype for this ( when the field type in Access is 'Datetime' ) – but I will play around to see if I can solve this………….

You have both been such a help – thank you

[lolacherrycola]

Hi again

Just to let you know, I set the field type from 'date' to 'text' in access / did an odbc import and now this seems to work……..the date is being seen as '01/01/2005' in MySQL – I will try setting this field back to 'date' format in Mysql to see what happens………..

I am really on my way now….thanks again both of you.

[Author]

Posts