Re: Replace a field with a calculated value

[J.O. Aho]

On 02/27/18 15:57, Jim H wrote:
>
> I inherited a table that has a column named "date_entered" that
> contains dates formatted like 1/30/2018 that I'd like to replace with
> the same date formatted as 2018-01-30.

What's the data type for the column in question?

if you don't know, you can run the query:
show columns in <table name>;


> How can I replace the whole content of the field (in just one record
> for now, but eventually every record) with a value calculated from the
> field being replaced?
>
> I wish I could do something like the following, but am hitting a dead
> end.
>
> REPLACE(date_entered, *, str_to_dat(date_entered, '%d/%m/%Y') WHERE
> record_no = '123';

for an update that takes them all:

update <table name> set date_entered = str_to_date(date_entered, '%d/%m/%Y')



> Does replace() even allow a field name as a parameter (meaning
> whatever is in that field)... or a wild card meaning the whole string
> whatever it is, or does it only allow specific string values?

The function replace() takes three arguments, the first is the string
you want to modify, the second is the sub-string you want to change and
the third one is with what, no regex.

sure you could use (at least in theory): date_entered =
replace(date_entered, date_entered, str_to_date(date_entered, '%d/%m/%Y')
but that would be a lot slower than the update row I suggested earlier.



Don't forget to change the code which inputs the date to use the format
you want. I do recommend to use a datetime data type for storing time,
as you can then get the format you want and change to include the time
at a later point without issues on a later date.

--

//Aho