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

Asked

Viewed 3,485 times

2

Guys, I’m extracting the 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 dynamic table.

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

2018-08-27 00:00:59

I do the query below:

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

The query returns the value "2018-08-27" as date. If I try to use the 103 date format 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 could find to leave the date format as dd/mm/yyyy was by making two Convert in the same query, however the field is as Varchar and I can’t leave it as Data. Follow the query:

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

That way the query return is "27/08/2018". But the field goes back to VARCHAR, if I try to make Select with another 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 searched several places and could not find anything to solve my problem.

Thank you.

sql-server string date sql-server-2014

  • In the first SELECT is already converting the VARCHAR for DATE and you say you want to know how to return as DATE. Can explain better the result you want?

  • Hello Pagotti, it is converting to DATE, but the format I want is dd/mm/yyyy. But it does not return this way, or it gives error, or returns as yyyy-mm-dd

  • 1

    The output format is either the current session configuration you are opening or you have to explicitly convert as you did in the second SELECT, but if you are going to apply any date function you have to do about the result of the first SELECT

  • 3

    A DATE has no format, only values (in this case, the numerical values of the day, month, year, etc.). When you make a select, these values are displayed in some format, of course, but that doesn’t mean that the date is in that format. For example, today is August 29, 2018. I can display it as "2018-08-29", "08/29/18", "Aug, 29th 2018" etc. The formats are different, but the value is the same (all these strings represent the same date: the same day, month and year of the calendar). If you want the date in a certain format, convert it to varchar. If you want to use the date itself, you do not need to convert

  • I get it. I decided to change the shape, so that it works in the Dynamics table, as I won’t be able to group the date, by month and year in the dynamics due to the field being text. I will create additional column with the month, and another with the Year, so the grouping will work. Anyway, thank you for the clarifications..

1 answer

0

I believe the query below may be what you are waiting for.

SELECT DATA, CAST( CONVERT(VARCHAR(10),TB_EPA.DATA,121) AS DATETIME) AS DATA_AJUSTADA
FROM TB_EPA

Browser other questions tagged sql-server string date sql-server-2014

You are not signed in. Login or sign up in order to post.