View Full Version : Date select between against a varchar column in non-mysql date format

07-27-2011, 11:04 AM
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

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

07-27-2011, 05:30 PM
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().


Old Pedant
07-27-2011, 09:27 PM
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.

07-28-2011, 10:05 AM
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

07-28-2011, 03:24 PM
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.

07-29-2011, 02:52 PM
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/