Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts

    Angry Date select between against a varchar column in non-mysql date format

    Small problem which I'm making a mess off

    I want to select count(*) from a table where column (Tdate) is between 2 dates.

    The issue is Tdate is imported from an access DB and as such is in the format
    "dd/mm/yyyy hh:mm:ss"

    so what I want to do is something similar to this

    Code:
    SELECT COUNT(*) FROM `tblskipmovements` WHERE Tdate>='2008-07-27 00:00:00'  AND Tdate<='2009-07-27 23:59:59'
    which obviously doesn't work against a varchar field in a different format.

    I've tried using the date format built in but failed any suggestions will be muchly appreciated
    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    As you know you can't use comparison operators on a date of that format, won't work. Try converting it to a date type using STR_TO_DATE().

    http://dev.mysql.com/doc/refman/5.0/...on_str-to-date

  • Users who have thanked Fumigator for this post:

    hinch (07-28-2011)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,550
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    But a better answer would be to convert all the dates ONE TIME in the DB so that you don't have to do STR_TO_DATE() every time you query. STR_TO_DATE() is a pretty slow function, so doing it once will take time but will save a bunch of query time in the long run.

  • Users who have thanked Old Pedant for this post:

    hinch (07-28-2011)

  • #4
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    i've tried converting it to a date using str_to_date() unfortunately for some reason it always returns null I think its due to it having a time data in the field (even though the time data is always 00:00:00)

    I do have one solution but I'm reluctant to do it since it'll increase the length of time of my import skip exponentially.

    so was kinda hoping there would be an easyier way of doing it in query. I based it on the fact the importer has to process 100k+ rows at a time where as in the report I'll only be selecing a subset of those rows to start with ie where clientid=1 first and then between startdate , enddate on dodgy access column.

    The annoying thing is all this is just a put me on until I can finish writing all the new internal systems to replace access with so then we can do everything in proper date/times rather than access's retarded date times
    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com

  • #5
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    You can force str_to_date to look at only the first 10 characters using substr(). And, it sounds to me like you have access to your Access database? Can you control the formatting of your import file? If you can possibly format the date on the Access side to put it in yyyy-mm-dd format then you can import to a date type column. I'm guessing you've thought of that already though.

  • #6
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    yeah i have access to the db and yeah I've already tried that access is REALLY **** with dates etc hello random # on date ranges

    anyway I went the route i was hoping to avoid in the end.

    instead of just using fgetcsv and an implode I actually get the line of data explode on , reformat the date column then implode the array again to give me my insert variables.

    its a really **** way of doing things but it works I was just hoping to avoid doing it this way mostly due to speed issues and page timeouts etc my previous way before date formatting took 2 seconds to import 100k rows now it takes like 70 seconds.

    but at the end of the day it now works so it'll do me. few months time when I've finished the production systems I can do away with access totally and never have to worry about it again because i'll be using site to site replication \o/
    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •