Menu
I have a date column which is in YYYYMMDD or 20120101 format. This is because SAP stores it in this format, so cannot change this.
How can I convert this to a YYYY-MM-DD format in a MySQL query? In DB2, I used a to_date() function.
In MySQL, I'm trying the STR_TO_STRING() function but it returns 'null'.
SELECT STR_TO_DATE(VBAP.ERDAT,'%Y-%m-%d') FROM VBAP
376
Convert Char 'yyyymmdd' back to Date data types in SQL Server. Now, convert the Character format 'yyyymmdd' to a Date and DateTime data type using CAST and CONVERT. --A. Cast and Convert datatype DATE: SELECT [CharDate], CAST([CharDate] AS DATE) as 'Date-CAST', CONVERT(DATE,[CharDate]) as 'Date-CONVERT' FROM [dbo].
MySQL uses yyyy-mm-dd format for storing a date value. This format is fixed and it is not possible to change it. For example, you may prefer to use mm-dd-yyyy format but you can't. Instead, you follow the standard date format and use the DATE_FORMAT function to format the date the way you want.
Use STR_TO_DATE() method from MySQL to convert. The syntax is as follows wherein we are using format specifiers. The format specifiers begin with %. SELECT STR_TO_DATE(yourDateColumnName,'%d.
MySQL retrieves and displays DATE values in ' YYYY-MM-DD ' format. The supported range is '1000-01-01' to '9999-12-31' .
This works..
date_format(str_to_date(VBAP.ERDAT, '%Y%m%d'),'%Y-%m-%d')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With