Convert to dd/MM/yyyy from character string

Question

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?

Simply use `101' instead of '103' and check https://stackoverflow.com/questions/17679009/convert-datetime-to-us-and-european — Ven, Apr 03 '18 at 09:40
*Don't* store dates as strings. Use the proper type, eg `date`. Anything else just covers up the bug. Converting a *string* to another *string* is a very strange request too — Panagiotis Kanavos, Apr 03 '18 at 09:44

Tim Biegeleisen · Answer 1 · 2018-04-03T09:43:07.010

2

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.

edited Apr 03 '18 at 09:43

answered Apr 03 '18 at 09:40

Tim Biegeleisen

I get this error: `The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value`. – Beatrice Toia Apr 03 '18 at 09:42
@BeatriceToia Your data is the problem, not my query, which is working in the demo. My guess is that you have some strings in there which can't be converted to dates. – Tim Biegeleisen Apr 03 '18 at 09:43
I used `DATALENGTH` on that field and I get 20 as result and not 10 as it should be. Is there something I can do? – Beatrice Toia Apr 03 '18 at 09:48
You need to figure out what data you actually have, and then proceed from there. You might need an addtional step to filter off records with bad data, or maybe do an update to bring them to convertible dates. – Tim Biegeleisen Apr 03 '18 at 09:49
@BeatriceToia why should it be *10*? If it's `nvarchar(10)` it will be 20 bytes long and still contain 10 characters. If the format is not what you thought though, you'll get conversion errors. Why assume it's a *US* string? `nvarchar` is still the *wrong* type to use to store dates though. SQL Server has a `date` type – Panagiotis Kanavos Apr 03 '18 at 09:50

score 0 · Answer 2 · answered Apr 03 '18 at 09:46

0

You should use

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

See the FORMAT

answered Apr 03 '18 at 09:46

Plamen Nikolov

Formating a *string* ? This will throw `Argument data type varchar is invalid for argument 1 of format function.` – Panagiotis Kanavos Apr 03 '18 at 09:46
@PanagiotisKanavos - this is exactly what I got. – Beatrice Toia Apr 03 '18 at 09:49
@BeatriceToia because formatting a string as a string is meaningless. Fix the field type. How can you be certain *all* strings use the US date format? – Panagiotis Kanavos Apr 03 '18 at 09:52

score 0 · Answer 3 · answered Apr 04 '18 at 13:30

0

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

answered Apr 04 '18 at 13:30

Aakash Singh