Converting to SQLDate

Getting Started
1

Hi,
I made a mistake to storing date in Mysql Database as a VARCHAR with this format 24-Apr-2018

Now, I need to do searching data(using query) between two dates.

Is there any simple way to convert it into Sqldate format , I ever do changing the format in mysql structure, from VARCHAR into date, but its erasing the values.
The data is more than 10,000 rows, its hard to change it manually.
Any Helps ?

Thanks
Arief

2

Make a small method that read the current database and create a second one (correct), then write the data from the first to the second. When you read the 24-Apr-2018, extract each date atom and build an SQLDate (2018-04-24) that you store in the nex data base.

Or… add a date presentation modifier… At read time change 24-Apr-2018 to looks like 2018-04-24.

Edit:
I hope you use JUN and JUL…

PS: use NthField to get the date atoms values…

For the query in the data base actual state, you can do it, but you have to be creative (make the search on each date atoms: D, M, Y).
I would use suggestion #1.

3

create a new field as a date in the same table, then copy the data from varchar to date in this new field using an update query
delete the old varcher field if everything is ok. rename the date field to the old varchar field name, so that it is like nothing has changed.

4

Jean-Yves: that is a creative answer: I love it !

5

UPDATE table SET newdate = CONCAT(SUBSTR(date, 8, 4), ‘-’,
IF(SUBSTR(date, 4, 3) = ‘Jan’, ‘01’,
IF(SUBSTR(date, 4, 3) = ‘Feb’, ‘02’,
IF(SUBSTR(date, 4, 3) = ‘Mar’, ‘03’,
IF(SUBSTR(date, 4, 3) = ‘Apr’, ‘04’,
IF(SUBSTR(date, 4, 3) = ‘May’, ‘05’,
IF(SUBSTR(date, 4, 3) = ‘Jun’, ‘06’,
IF(SUBSTR(date, 4, 3) = ‘Jul’, ‘07’,
IF(SUBSTR(date, 4, 3) = ‘Aug’, ‘08’,
IF(SUBSTR(date, 4, 3) = ‘Sep’, ‘09’,
IF(SUBSTR(date, 4, 3) = ‘Oct’, ‘10’,
IF(SUBSTR(date, 4, 3) = ‘Nov’, ‘11’,
IF(SUBSTR(date, 4, 3) = ‘Dec’, ‘12’, ‘’)))))))))))) , ‘-’, SUBSTR(date,1, 2))

6

Seems to be easy to do it,
I will do some test for all the solutions.

Thanks
Regards,
Arief

7

[quote=384223:@Marius Dieter Noetzel]UPDATE table SET newdate = CONCAT(SUBSTR(date, 8, 4), ‘-’,
IF(SUBSTR(date, 4, 3) = ‘Jan’, ‘01’,
IF(SUBSTR(date, 4, 3) = ‘Feb’, ‘02’,
IF(SUBSTR(date, 4, 3) = ‘Mar’, ‘03’,
IF(SUBSTR(date, 4, 3) = ‘Apr’, ‘04’,
IF(SUBSTR(date, 4, 3) = ‘May’, ‘05’,
IF(SUBSTR(date, 4, 3) = ‘Jun’, ‘06’,
IF(SUBSTR(date, 4, 3) = ‘Jul’, ‘07’,
IF(SUBSTR(date, 4, 3) = ‘Aug’, ‘08’,
IF(SUBSTR(date, 4, 3) = ‘Sep’, ‘09’,
IF(SUBSTR(date, 4, 3) = ‘Oct’, ‘10’,
IF(SUBSTR(date, 4, 3) = ‘Nov’, ‘11’,
IF(SUBSTR(date, 4, 3) = ‘Dec’, ‘12’, ‘’)))))))))))) , ‘-’, SUBSTR(date,1, 2))[/quote]

Where I can do this, in xojo or in phpmyadmin mysql page ?

thanks
Arief

8

No XOJO, just SQL. Do it in phpmy´admin. But first you have to create the “new” field newdate as date…

9

Or you can use str to date.
https://www.w3schools.com/sql/func_mysql_str_to_date.asp

You have a date like: 24-Apr-2018 so:
day-abbrevetion_mont-year_4
so:
%d-%b-%Y

SELECT * from {MY_TABLE}
WHERE STR_TO_DATE({MY_FIELD}, ‘%d-%b-%Y’) >= {MY_DATE_START}
AND STR_TO_DATE({MY_FIELD}, ‘%d-%b-%Y’) < {MY_DATE_END}

10

Its very fast…

Thanks…

Regards
Arief