0

I currently have a column ('kWhdate') in a view ('appdatatest') that takes a timestamp column and converts it to a date.

 date_format(`t`.`value_timestamp`,'%d/%m/%y%y') AS `kWhDate`

The above is part of the Create View query. It is currently stored as VARCHAR(10). I would like to convert it to a Date. I have tried the following:

 ALTER VIEW appdatatest MODIFY appdatatest.kWhDate datetime
 SELECT STR_TO_DATE(appdatatest.kWhDate, Date) FROM appdatatest

I have looked at this question Is it possible to change the datatype of a column in a view? but it gives an answer for SQL Server not MYSQL

If possible I could convert in the original CREATE VIEW query

Thanks

RDC_Green
  • 57
  • 5

1 Answers1

0

You can convert a timestamp to a date with date().

SELECT date(kwhdate) kwhdate
       FROM appdatatest;
sticky bit
  • 36,626
  • 12
  • 31
  • 42
  • Thanks @sticky bit, but I’m trying to change the structure from varchar to date in a view. The reason is I need to pull the data through as date as I can’t change it in Power BI – RDC_Green Dec 15 '20 at 21:06