Convert DATA dd/mm/yyyy hh:mm:ss to yyyy/mm/dd

Asked

Viewed 49,720 times

9

I have a date in the following format 14/12/2015 00:00:00 and need to convert to 2015-12-14 as I can do it on SQL?

sql type-conversion sql-server-2012

  • You want to do this in sql statement or in your application?

  • You need to specify more your question.

  • 1

    Which database you are using?

  • 2

    I am used SQL Server 2012

  • 2

    @Lilloh, you have your answer ?

  • @Lilloh Did you deny my answer? Why?

Show 1 more comment

6 answers

7


6

DECLARE @data datetime=GetDate()

SELECT concat(YEAR(@data),'-',MONTH(@data),'-',DAY(@data)) As data_convertida

Concat function is valid only from SQL Server 2012 if you do not have this SQL use as follows

SELECT cast(YEAR(@data) as varchar(4))+'-'+cast(MONTH(@data)  as varchar(2))+'-'+cast(DAY(@data) as varchar(4)) As data_convertida

Note: Note that in this case I used a variable of type datetime Using the DAY(), MONTH() and YEAR() functions, SQL automatically knows where to find the values for each of the values it asks for in the function.

What type of variable/data are you trying to convert to this format

0

EDITED: After some tests performed I observed that the function CONVERT will only work if the variable that will be formatted/converted is of the type Date.

Use the function CONVERT(type_given(size), variable, style).

The first parameter is the type of data that will be formatted, the second parameter is the variable that will convert and the third is the code of the pattern you want, in this case 111 corresponds to yyyy/mm/dd.

CONVERT(VARCHAR(10), GETDATE(), 111);
  • 1

    It seems not. I tested this command and it brings in the format dd/mm/yyyy

  • @Albertocláudiomandlate really wasn’t working, my error was in using string to convert. The original variable must be of the type Date, then it will work.

  • @Lilloh I made a small change in response, check again, please.

  • Hi, I took the test by changing the data type to Date and it really worked. It seems to me also a good tip

-1

SELECT convert(datetime, '14/12/2015 00:00:00', 103)

-1

Try this:

SELECT CONVERT(VARCHAR(10), GETDATE(), 120)

Or this

select date(Columnname) from tablename

  • For people who negatived, it would be nice a comment. =/

  • Code 103 corresponds to dd/mm/yyyy. I believe that’s the reason why you and @Paulohdsousa’s answers have been denied

  • 1

    It gives nothing no @Pedrocamarajunior. There will always be this. We don’t always have time for an answer all decked out, the time is short. But I help how I can.

  • Thank you @Pedrocamarajunior

-1

in MYSQL you can use DATE_FORMAT()

Browser other questions tagged sql type-conversion sql-server-2012

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