#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Dec 2010
    Location
    Taunton UK
    Posts
    20
    Rep Power
    0

    Migrating from access to MySQL


    I have developed a site using an access database but would like other sites to be able to draw from the information so decided to migrate to MySQL. The only issue I have is the date. When a date is now submitted ie using date() to automatically update when a record is changed, or a javascript date picker I get 0000-00-00 instead of the correct date. Also if I have a set of dates in, where as before with the access database I could choose date ranges to display, it now doesn't work.

    Like I said it all worked using access. Anything obvious to do?
  2. #2
  3. Couch Potato Wizard
    ASP Mastermind (5000+ posts)

    Join Date
    Jan 2005
    Location
    India
    Posts
    13,651
    Rep Power
    2688
    You didnt post your code. But in asp, we use # around date for Access in sql query.
    But for MySql, you'll need single quotes (') around dates.

    It might help if you post related code and problems!
    Laziness is my religion and Sunday is my God

    Get the Mantra!
  4. #3
  5. Senior Water Wizard
    ASP Mastermind (5000+ posts)

    Join Date
    Aug 2004
    Location
    Cape Town, RSA
    Posts
    10,233
    Rep Power
    862
    Hi, the default date command in mySQL is now() not date()

    Comments on this post

    • micky agrees
    Look! Its a ShemZilla


  6. #4
  7. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Dec 2010
    Location
    Taunton UK
    Posts
    20
    Rep Power
    0
    Apologies for no code.... thought there might be an obvious fix in relation to one database and the other.

    The issue seems to be with the date picker .js, I have switched form European date format to US and it now works regarding putting the correct date in the database. However I now have the following issue:

    When I select the date... displays as YYYY-MM-DD, click update, reverts to European format which is what I want, if you then select calendar again an error message occurs which states accepted format YYYY-MM-DD. I'll pursue this through the calendar forum.

    Regarding ASP and dates the following statement works for access

    FROM events WHERE active=1 AND EventDate > (DATE()-1) ORDER BY EventDate ASC"

    However for MySQL I get the following error....

    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

    [MySQL][ODBC 3.51 Driver][mysqld-5.5.8]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 ')-1)ORDER BY EventDate ASC' at line 1

    /events.asp, line 24

    Removing AND EventDate > (DATE()-1) displays all events correctly.
  8. #5
  9. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Dec 2010
    Location
    Taunton UK
    Posts
    20
    Rep Power
    0
    Originally Posted by nofriends
    Hi, the default date command in mySQL is now() not date()
    I want events to display for the current day as well. (DATE()-1) does that for access. However (now()-1) displays no records and (now) does not display the current date but all records after.
  10. #6
  11. Senior Water Wizard
    ASP Mastermind (5000+ posts)

    Join Date
    Aug 2004
    Location
    Cape Town, RSA
    Posts
    10,233
    Rep Power
    862
    the problem is that mySQL has different built in function for working with dates
    here is a good site
    you can use the date_sub function:
    Code:
    FROM events WHERE active=1 AND EventDate > data_sub(now(), INTERVAL 1 DAY) ORDER BY EventDate ASC"
  12. #7
  13. Couch Potato Wizard
    ASP Mastermind (5000+ posts)

    Join Date
    Jan 2005
    Location
    India
    Posts
    13,651
    Rep Power
    2688
    I cannot comment on the calender you using!

    But for today's records, this should work
    Code:
    FROM events WHERE active=1 AND EventDate = now() ORDER BY EventDate ASC
    Now() returns present date and time, so if you just need present date and not time, then you can use CURDATE() instead of NOW()

    For getting records for say, yesterday, then use Date_sub, like this
    Code:
    FROM events WHERE active=1 AND EventDate <= DATE_SUB(now(),INTERVAL 1 DAY) ORDER BY EventDate ASC
    Look here for reference

    http://dev.mysql.com/doc/refman/5.1/...functions.html
  14. #8
  15. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Dec 2010
    Location
    Taunton UK
    Posts
    20
    Rep Power
    0
    Originally Posted by micky
    I cannot comment on the calender you using!
    Do you have one you recommend?

    Regarding.....
    Originally Posted by micky
    Code:
    FROM events WHERE active=1 AND EventDate <= DATE_SUB(now(),INTERVAL 1 DAY) ORDER BY EventDate ASC
    And.....
    Originally Posted by nofriends
    Code:
    FROM events WHERE active=1 AND EventDate > data_sub(now(), INTERVAL 1 DAY)
    Neither worked, however thanks for the pointer guys after reading through.....

    http://dev.mysql.com/doc/refman/5.1/...functions.html

    The following worked:

    Code:
    FROM events WHERE active=1 AND EventDate > SUBDATE(now(),1) ORDER BY EventDate ASC
  16. #9
  17. Senior Water Wizard
    ASP Mastermind (5000+ posts)

    Join Date
    Aug 2004
    Location
    Cape Town, RSA
    Posts
    10,233
    Rep Power
    862
    glad you got it working!
  18. #10
  19. Couch Potato Wizard
    ASP Mastermind (5000+ posts)

    Join Date
    Jan 2005
    Location
    India
    Posts
    13,651
    Rep Power
    2688
    Glad i could point you to a direction

    I dont have any calender to refer you to at this moment, try searching on google.
  20. #11
  21. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Dec 2010
    Location
    Taunton UK
    Posts
    20
    Rep Power
    0
    Originally Posted by micky
    Glad i could point you to a direction

    I dont have any calender to refer you to at this moment, try searching on google.
    No problems - will do if I don't get any joy with the one I was using.... thanks again.

    Neil
  22. #12
  23. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Dec 2010
    Location
    Taunton UK
    Posts
    20
    Rep Power
    0

    Displaying UK date format whilst inputting into MySQL yyyy-mm-dd problem solved......


    Thought I'd post this as when I was looking for a solution it seemed a common question.

    I've changed the date picker to:
    http://www.dynarch.com/static/jscalendar-1.0/index.html

    Basically two input boxes:
    Code:
    <input name="EventDate" type="hidden" id="EventDate" size="10" />
    <input name="EventDateDisplay" type="text" id="EventDateDisplay" value="<%=EventDate%>" size="10" />
    Use EventDateDisplay to select the UK format date, the javascript below converts it to MySQL DB format and adds it to the EventDate input field. When you update, you get the correct (UK) date format displayed, but the correct DB date format is transmitted. It was a bit of code which was used to input one date and give you another a week later, I modified it to give an alternative date format in the second field.

    Code:
    <script type="text/javascript">
        function catcalc(cal) {
            var date = cal.date;
            var time = date.getTime()
            // use the _other_ field
            var field = document.getElementById("EventDateDisplay");
            if (field == cal.params.inputField) {
                field = document.getElementById("EventDate");
             
            } else {
     
            }
            var date2 = new Date(time);
            field.value = date2.print("%Y-%m-%d");
        }
        Calendar.setup({
            inputField     :    "EventDate",   // id of the input field
            ifFormat       :    "%Y-%m-%d",       // format of the input field
            showsTime      :    false,
            timeFormat     :    "24",
            onUpdate       :    catcalc
        });
        Calendar.setup({
            inputField     :    "EventDateDisplay",
            ifFormat       :    "%d-%m-%Y",     // format of the display field
            showsTime      :    false,
            timeFormat     :    "24",
            onUpdate       :    catcalc
        });
    </script>

    Comments on this post

    • micky agrees : Thanx for sharing the solution;)

Similar Threads

  1. Syntax error in INSERT INTO statement
    By kalcy in forum ASP Development
    Replies: 2
    Last Post: April 18th, 2007, 06:41 AM
  2. Convert Access tables to MYSQL tables
    By filterfann in forum SQL Development
    Replies: 2
    Last Post: January 2nd, 2006, 09:13 AM
  3. Access mysql database using JavaScript
    By thayalan44 in forum HTML, JavaScript And CSS Help
    Replies: 2
    Last Post: January 18th, 2005, 03:54 PM
  4. Access -> mySQL
    By Mighty in forum Microsoft Access Help
    Replies: 2
    Last Post: February 25th, 2004, 01:18 PM
  5. 'Insert Into' Statement Doesnt Work...Please Help.
    By shanu_harsh in forum ASP Development
    Replies: 2
    Last Post: February 17th, 2004, 02:38 PM

IMN logo majestic logo threadwatch logo seochat tools logo