CODEWITHSUNDEEP
sqlsql-server
WhoaItsAFactorial
WhoaItsAFactorial

Reputation: 3558

Convert dd/mm/yy string to yyyy-mm-dd

I am having issues converting a string in dd/mm/yy hh:mi:ss format to yyyy-mm-dd hh:mi:ss format.

I have tried using CONVERT() as follows:

select CONVERT(VARCHAR, '11/10/11 10:56:58', 120)

But that returns no change:

11/10/11 10:56:58

Upvotes: 1

Views: 34669

Answers (3)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

You need to convert to datetime first to change a string to reflect a certain regional format. Be sure you are interpreting the datetime value correctly, too; on some systems, that will be October 11th, on others it will be November 10th.

SELECT CONVERT(CHAR(19), CONVERT(DATETIME, '11/10/11 10:56:58', 3), 120);

Finally, use the correct number of characters in your char or varchar declarations. Not specifying a length is lazy and can lead to problems. See:

Upvotes: 8

Francis P
Francis P

Reputation: 13655

The issue: you are converting a VARCHAR to a VARCHAR.

Your query is fine if you use a DATETIME.

SELECT CONVERT(VARCHAR(19), CAST('11/10/11 10:56:58' AS DATETIME), 120);

See fiddle.

Upvotes: 1

Lamak
Lamak

Reputation: 70638

This will work:

SELECT CONVERT(VARCHAR(19),CONVERT(DATETIME,'11/10/11 10:56:58',3),120)

Upvotes: 4

Related Questions