Range of dates VARCHAR/DATE

Asked

Viewed 673 times

2

I have a date field (type VARCHAR) in my table, and recently I needed to search the database with date range.

And for that I used the operator Between, getting something like: BETWEEN 15/08/2015 AND 25/08/2015. So far so good.

The problem is when I need to search from one month to another, something like: BETWEEN 25/07/2015 AND 25/08/2015, ai does not work, and I believe faithfully that this is due to the problem of the field date be of type VARCHAR and not the date type.

I am willing to change the format of the dates to 0000-00-00, the problem is that when capturing this data I cannot show in this format, since here the default is 00/00/0000.

Well, I need to solve this, as I do to conform the format 0000-00-00 in 00/00/0000, or another way to search the range of records, in order not to use the date field for this.

mysql sql date

  • Really I would convert the guy but to solve the search tried STR_TO_DATE(data, '%d-%m-%Y')? If this is the format your date is in.

  • Solves part of the problem Convert Mysql data dd/mm/yyyy to yyyy-mm-dd

  • If you plan to change the date format, it is not better to change the field type to DATE, then? That would solve all your problems and the bank remains consistent.

1 answer

3


I think the best way is to do the conversion. I would have to create a new column like DATE and make a UPDATE to record in this column the date taking from the old column that is still as VARCHAR. Probably using this: STR_TO_DATE(data, '%d-%m-%Y').

UPDATE tabela SET datanova = STR_TO_DATE(data, '%d-%m-%Y')

Then you delete the old column and rename the new one. Of course all applications accessing this table need to be prepared for this change.

This same expression can be used to convert the column when searching, if you prefer not to convert. So you can use the date normally.

SELECT * FROM tabela WHERE STR_TO_DATE(data, '%d-%m-%Y')
                           BETWEEN '2015/08/15' AND '2015/08/25'

I put in the Github for future reference.

I’m considering that your date is separated by -. If not this, change the tab.

  • bigown, I tried this way but it doesn’t seem to work, see that I have to search between formatted dates and not as they were before, so it is correct: SELECT * FROM tabela WHERE STR_TO_DATE(date, '%d/%m/%Y') BETWEEN STR_TO_DATE('15/10/2015', '%d/%m/%Y') AND STR_TO_DATE('18/10/2015', '%d/%m/%Y'), in my case the tab is / and the date field is date.

Browser other questions tagged mysql sql date

You are not signed in. Login or sign up in order to post.