Date Range VARCHAR / DATE

Navigation
2

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

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

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

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

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

    
asked by anonymous 27.09.2015 / 01:30

1 answer

3

I think the best way is to do the conversion. You would have to create a new column of type DATE and make a UPDATE to write in that column the date picking up 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 column. Of course, all applications that access this table need to be prepared for this change.

This same expression can be used to convert the column at the time of the search 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'm considering that your date is separated by - . If not, change the tab.

    
27.09.2015 / 01:45
Display different objects with equal groups Guzzle and asynchronous requests in PHP