I have a table called sale where I have a field that saves the date DD / MM / YYYY the type of this field is text now that I need to make filters with that date I have had problems with consulting the information well, as I understand the idea is that field was as DATE.
I would like to know what is the ideal way to be able to pass this field to DATE and put the correct format YYYY-MM-DD I do not know if the same field can be updated to create another field called venta_at where the date is saved with the DATE format .
x
UPDATE "sale" SET "sale.sale_date" =
CAST( SUBSTRING ( "sale_at", 7, 4 ) ¦¦ '-' ¦¦
SUBSTRING ( "sale_at", 4, 2 ) ¦¦ '-' ¦¦
SUBSTRING ( "sale_at", 1, 2 )
AS DATE)
executing this throws me an error
1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘”sale” SET “sale.sell_date” =
CAST (SUBSTRING (“venta_at”, 7, 4) ¦¦ ‘at line 1
Advertisement
Answer
Using: STR_TO_DATE
UPDATE venta SET sale.sale_date = STR_TO_DATE(sale_at, "%d / %m / %Y")