Advertisement

how do I pass a date in text format to DATE format with sql [closed]

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 .

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")

Advertisement