SELECT WITH STRING FORMATTING

Question

Using the Mysql Database, intending to perform the query in a given table whose one of its fields data has as type VARCHAR(8).

Query SQL - Current

SELECT `t_movimentacao`.`data` FROM t_movimentacao 
WHERE `t_movimentacao`.`id` = '1';

Running the current query we get the following return: 23072019

|   data   |
- 23072019 -

Knowing that the string contains a date in the format dd/mm/yyyy, without the character of / how we can format and customize this field directly in the same query.

I want an SQL Query - Formatting the date field to obey the second result in the query: 23/07/2019

|    data    |
- 23/07/2019 -

How to format the string to place the / and receive the date in the desired format directly in SQL?

Answer 1

If you don’t need to work with DATA, only for formatting and presentation from sql you can use the function CONCAT and SUBSTR. The function CONCAT concatenates one or more parameters and the SUBSTR function extracts a substring from a string by passing the string the start you want to start and how many characters. If you need to, I believe you will have q convert the string to date and consider mysql configuration.

SELECT
  CONCAT(SUBSTR(t_movimentacao.data, 1, 2), "/", SUBSTR(t_movimentacao.data, 3, 2), "/", SUBSTR(t_movimentacao.data, 5, 4))
FROM t_movimentacao
WHERE t_movimentacao.id = '1';

Answer 2

SELECT cast(left(data,2) as nvarchar) + '-' + SUBSTRING(data,3,2) + '-' + 
cast(RIGHT(data,4) as nvarchar) as data FROM t_movimentacao 
WHERE id = 1;

Answer 3

SELECT DATE_FORMAT(`t_movimentacao`.`data`, '%d / %m / %y') FROM t_movimentacao 
WHERE `t_movimentacao`.`id` = '1';

see if it goes like this