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.
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
SELECT CONVERT (VARCHAR (20), YourField, 103) FROM YourTable
You can view the formats here: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql
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
