Change varchar field yyyy-mm-dd hh: mm: ss to date dd / mm / yyyy in SQL Server

3

Dear, I'm extracting data from a satisfaction survey log, and I'm trying to create a column with the date format, so I can group the days and months into an Excel PivotTable.

For more details, follow the field varchar with the date:

2018-08-27 00:00:59

I make the query below:

SELECT DATA, CONVERT(DATE,TB_EPA.DATA,121) AS DATA_AJUSTADA
FROM TB_EPA

The query returns me the value "2018-08-27" as the date. If I try to use the date format 103 which is the format I want to convert, SQL returns me the following error:

  

Conversion failed when converting date and / or time from character string.

The only way I found so far to leave the date format as dd / mm / yyyy was to make two Convert in the same query, however the field stays as Varchar and I can not leave it as Data. Here is the query:

SELECT DATA, CONVERT(VARCHAR,CONVERT(DATE,TB_EPA.DATA,121),103) AS DATA_AJUSTADA
FROM TB_EPA

In this way the query return is "27/08/2018" . But the field returns to VARCHAR, if I try to make Select with one more Convert so that the value is returned as Data:

SELECT DATA, CONVERT(DATE,CONVERT(VARCHAR,CONVERT(DATE,TB_EPA.DATA,121),103),103) AS DATA_AJUSTADA
FROM TB_EPA

The query returns 2018-08-27

Would you know any way to return this field as date (dd / mm / yyyy)? I researched in several places and could not find anything that solved my problem.

Thank you.

    
asked by anonymous 29.08.2018 / 13:04

1 answer

0

I think the query below might be what you're expecting.

SELECT DATA, CAST( CONVERT(VARCHAR(10),TB_EPA.DATA,121) AS DATETIME) AS DATA_AJUSTADA
FROM TB_EPA
    
29.12.2018 / 17:47