Convert '01-Sep-2017' to '01/09/2017' in T-SQL?

Question

I have developed an application in C# / WPF which saved nvarchar columns in the table in two formats, one is with hyphens and on with forward dashes, like so: '01-Sep-2017' to '01/09/2017'

The simplest solution would be to convert the date which is in format '01-Sep-2017' to '01/09/2017' while in query and get the data as it should be?

How can I do that?

Or should I use some sort of update statement to convert and save the date into appropriate format?

Note: but if would not be easy. I have different data for the two formats. It should be collapsed into the same date.

ALTER TABLE xxxx ALTER COLUMN yyyy DATETIME (remove all the existing ones first) — Steve, Mar 26 '18 at 14:28
@RBarryYoung lol, yes. But these old people here would not listen :) — Vlad, Mar 26 '18 at 14:28
The `'01/09/2017` notation is ambiguous. `/` suggest a US notation, while putting the day before the month is typical for just a handfull of countries. If you really need to storee date as string, use ISO notation `YYYY-MM-DD` See: https://en.wikipedia.org/wiki/ISO_8601 — Stefan, Mar 26 '18 at 14:30
@Steve: perhaps it's better to improves Vlad's persuasion skills. As far as you know, you could be using their software somehow, better make it right ;-) — Stefan, Mar 26 '18 at 14:32
You could convert your string to datetime, then convert back to string. CONVERT(datetime, '01-Sep-2017', 103) Then .strftime('%m/%d/%Y') — Josh Adams, Mar 26 '18 at 14:32
Go back to them and tell them that they are wrong. You may feel free to refer them to me or ***any other SQL expert on the planet***, including many Microsoft articles. — RBarryYoung, Mar 26 '18 at 14:33
...Or just send them to read Aaron Bertrand's [Bad habits to kick : choosing the wrong data type](https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type). — Zohar Peled, Mar 26 '18 at 14:36
You can count me in the group that says storing dates as strings is horrific. — Sean Lange, Mar 26 '18 at 14:36
Storing dates as strings is such a bad idea that it should be illegal. — MJH, Mar 26 '18 at 14:39
@Stefan if I were to guess OP is probably working in the government. Any objection to the old employees' decision is going to get you fired. Because that's the only way to keep themselves there and not get replaced by more skillful ppl. — Steve, Mar 26 '18 at 14:40
@Steve The database had a bad design already, but its too late to change it now. The application is in use now. I could change it during the summer. ;) — Vlad, Mar 27 '18 at 08:01

score 2 · Accepted Answer · answered Mar 26 '18 at 14:32

2

You would need to be sure that your SQL Server is installed in English. Once you are sure about that the convert function should do the job

SELECT CONVERT(DATETIME, '02-Sep-2017')

And if you need to turn that into that string you shared:

SELECT CONVERT(VARCHAR, CONVERT(DATETIME, '02-Sep-2017'), 103)

Style Reference here

answered Mar 26 '18 at 14:32

hardkoded

18,915
3
52
64

hmm, tried it, I get this error: The conversion of a nvarchar(50) data type to a datetime data type resulted in an out-of-range value. – Vlad Mar 27 '18 at 08:32
Hmm. I am using subquery in a subquery. Need to search info. – Vlad Mar 27 '18 at 08:40
@Vlad I believe one value is breaking the conversion, is your system in English? – hardkoded Mar 27 '18 at 10:59
yes the fields I was converting can have NULL values. – Vlad Mar 27 '18 at 11:05
@harkoded hmm, that is strange. But why do i get the error. It works if I skip the nulls – Vlad Mar 27 '18 at 13:42
This works DEClARE @var NVARCHAR(50) = '02-Sep-2017' SELECT CONVERT(VARCHAR, CONVERT(DATETIME, @var), 103) – hardkoded Mar 27 '18 at 13:44

score 2 · Answer 2 · edited Mar 26 '18 at 14:35

2

SELECT CONVERT (VARCHAR (20), YourField, 103) FROM YourTable

You can view the formats here: https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

edited Mar 26 '18 at 14:35

Josh Adams

2,113
2
13
25

answered Mar 26 '18 at 14:33

SQL_M

2,455
2
16
30

All comments are already made. This is probably no best practice, to say the least. – SQL_M Mar 26 '18 at 14:33

Zohar Peled · Answer 3 · 2018-03-27T09:56:46.310

As noted by practically everyone on the comments, and anyone that knows what they are doing when it comes to databases, storing dates as strings is a terrible idea, that can only lead to problems.
Here is a (very) short list of reasons:

No way to verify that the stored value is actually a date - nothing is stopping you from storing '13/09/2017' in one row and 'Banana' in another one.
No way to verify validity of dates - nothing is stopping you from storing '13/14/1000' or '31-Feb-2020' or even '01-Dev-1999'
No way to use any of the database built in date/datetime functions without casting to date first.
No way to perform date range searches without casting to date first.
No way to enforce any kind of date-based logic (i.e you have a start date and end date columns, and you want to make sure that end date is either null or later then start date)
String representation of dates are culture-specific. '01-Dez-2017' is December first 2017, in German, for instance.
The .Net framework DateTime struct maps directly to SQL Server Date, DateTime, DateTime2 data types. Storing dates as strings means you have to do extra work passing dates between the application layer and the database.

Having said all that, If you still absolutely can't refactor your database to store dates properly, you can use convert to change the string representation of dates to proper dates. Since it's 2008 version, Try_convert is off the table, so you will need to use a couple of common table expressions to handle the different string representations of dates:

CREATE TABLE Terrible
(
    StringDate varchar(20)
);

INSERT INTO Terrible VALUES
('01-Sep-2017'), ('01/09/2017'),('Banana'),('30/02/2017');


SET DATEFORMAT DMY;
SELECT CONVERT(char(10), DateValue, 103)
FROM
(
    SELECT CONVERT(Date, REPLACE(StringDate, '-', ' '), 106) As DateValue
    FROM Terrible
    WHERE StringDate LIKE '[0-9][0-9]-[a-z|A-Z][a-z|A-Z][a-z|A-Z]-[0-9][0-9][0-9][0-9]%'

    UNION ALL

    SELECT CONVERT(Date, StringDate, 103) As DateValue
    FROM Terrible
    WHERE StringDate LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]'
    AND ISDATE(StringDate) = 1
) ProperDates

Convert '01-Sep-2017' to '01/09/2017' in T-SQL?

3 Answers3

Linked