3

I have a table in a MySQL database with a field named 'Date', the problem is the date is in the format DD-MM-YYYY so I cannot upload it to MySQL as a DATE type field. Instead the field type is a string. With this in mind, how can I write query that will give this effect - 

SELECT * FROM `table` 
WHERE  (date_field BETWEEN '2010-01-30' AND '2010-09-29')

keeping in mind 'date_field' is not of DATE type, but string.

jII
  • 1,134
  • 2
  • 17
  • 29
  • check this link, hope you will find some way out http://stackoverflow.com/questions/1408285/mysql-change-date-string-to-date-type-in-place – vpv Jun 07 '12 at 07:19
  • Is the `date_field` in format `DD-MM-YYYY` as the question states, or `YYYY-MM-DD` as the query indicates? If it's the latter, `BETWEEN` should work because the dates are in alphabetical order. – Andrew Leach Jun 07 '12 at 07:38

3 Answers3

16
SELECT * FROM `table` 
WHERE str_to_date(date_field, '%d-%m-%Y') BETWEEN '2010-01-30' AND '2010-09-29'

SQLFiddle example

Edit

You can also do this:

SELECT * FROM `table` 
WHERE cast(substring(date_field, 7, 4) as unsigned) * 10000 + cast(substring(date_field, 4, 2) as unsigned) * 100 + cast(substring(date_field', 1, 2) as unsigned)
BETWEEN 20100130 AND 20100929
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • And if you're very paranoid, you can `str_to_date()` all the strings, not just the first one. – Álvaro González Jun 07 '12 at 07:19
  • I don't think that is necessary :) – juergen d Jun 07 '12 at 07:20
  • Unfortunately this query is not returning any result. I am positive I have data between the dates I chose. What do you suggest? – jII Jun 07 '12 at 07:25
  • @jesterII: That was my mistake. Use `Y` instead of `y` in `str_to_date` – juergen d Jun 07 '12 at 07:38
  • Why are you converting a date value to a date value again? shouldn't you convert the strings to dates instead? – Hugo Jun 07 '12 at 07:39
  • `str_to_date` does convert the strings to a date. – juergen d Jun 07 '12 at 07:40
  • Still not working... Is there a way to convert a substring of the date_field to an integer? for example, to get the year as an integer I would do something like: `CONVERT substring(date_of_call, 7, 11) to INTEGER` – jII Jun 07 '12 at 08:10
  • @jesterII: What does not working mean? Did you see the SQLFiddle example. It works there. – juergen d Jun 07 '12 at 08:14
  • The SQL fiddle example inserts the dates in the YYYY-MM-DD format, but in my database the date is in DD-MM-YYYY. Not working means that the `if (!mysql_query($query))` test returns `false` – jII Jun 07 '12 at 08:16
  • @jesterII: Actually the SQLFiddle example insert exactly like you want it. But see my update. – juergen d Jun 07 '12 at 08:20
  • 1
    @juergend my apologies I was looking at Hugo's SQL fiddle example in the other solution. But your edit worked great, thank you very much. – jII Jun 07 '12 at 08:37
8

Fix the table!

  1. Add a new column of the appropriate DATE data type:

    ALTER TABLE `table` ADD new_date_field DATE AFTER date_field;

  2. Use MySQL's STR_TO_DATE() function to populate that new column with the dates held in the old column:

    UPDATE `table` SET new_date_field = STR_TO_DATE(date_field, '%d-%m-%Y');

  3. Drop the old column (and, if so desired, rename the new one in its place):

    ALTER TABLE `table` DROP date_field, CHANGE new_date_field date_field DATE;

  4. Change your application to use this new column.

eggyal
  • 122,705
  • 18
  • 212
  • 237
0

Having worked with Dates in various formats, I have found it best to either format back to the database format, or your own consistent format for processing. One option we use is our own date time format, which is simply YYYYMMDDhhmmss, which will always sort correctly for leap years, etc... and makes parsing data easy. We can then display the format in the user's desired view, without any problems.

While this is not a direct answer to your question, I have found this saves a lot of time and trouble when processing date and time formats from different countries. We also tend to save the time in Zulu time.

Steven Scott
  • 10,234
  • 9
  • 69
  • 117