CODEWITHSUNDEEP
sqlsql-serversql-convert
Beatrice Toia
Beatrice Toia

Reputation: 11

Convert to dd/MM/yyyy from character string

I have the following output that is stored in a string field:

Query

SELECT StringField5 FROM MyTable WHERE OrderID=1

Output

02/13/2018

I would like to use one of the ways SQL allow to convert/cast to get the following output:

13/02/2018.

I have tried the following code and a lot of them found on this website but none helped and always returned the first output described (02/13/2008):

SELECT CONVERT(varchar(25), StringField5, 103) FROM MyTable WHERE OrderID=1

I'm using SQL Server 2016.
What am I missing?

Upvotes: 1

Views: 1573

Answers (3)

Aakash Singh
Aakash Singh

Reputation: 1062

you can use this method to convert from '02/13/2018' to '13/02/2018'

Select substring(StringField5,4,2)+'/'+ substring(StringField5,0,3)+'/'+ substring(StringField5,7,4) FROM MyTable WHERE OrderID=1

Upvotes: 0

Plamen Nikolov
Plamen Nikolov

Reputation: 2733

You should use 

SELECT FORMAT(StringField5, 'dd/MM/yyyy', 'en-US' ) FROM MyTable WHERE OrderID=1

See the FORMAT

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

First convert your string date to a datetime, then convert that datetime back to the string output you expect:

SELECT CONVERT(varchar(25), CONVERT(datetime, '02/13/2018', 101), 103)
FROM MyTable
WHERE OrderID = 1;

Demo

By the way, it is generally a bad idea to store your dates in the database as strings. Always store your information as formal dates.

Upvotes: 2

Related Questions