Convert string to date in MS SQL Server

Question

I have table in which there is a column named period_start which is type of nvarchar. And it has different types of date formats.

Ex.

 1. 01122018 --ddmmyyyy

 2. 01132018 --mmddyyyy

 3. 20181802 --yyyyddmm

 4. 20180214 --yyyymmdd

 5. 2018-01-02--yyyy-mm-dd

 6. 01/02/2018 --dd-mm-yyyy

 7. 010218 --ddmmyy

These are the dates which i want to convert in database date format.

I tried writing this code but wasn't successful.

Create Function dbo.[ConvStrToDate]
(@str nvarchar(50))
Returns Date
As
Begin
Declare @ConvertedDate Date;
Set @str = convert(date,@str,104)
Return @ConvertedDate
End

Real Data Example..

So your data has loads of different representations of a date? How do you know that '01122018' is 01/12/2018 and not 12/01/2018? Unless you can give us a defined reason as to how to determine this, your task is impossible. I'm afraid you are stuck with the poor choice of datatype, and (effectively) your data is completely meaningless. — Thom A
– Thom A ♦, Commented Mar 6, 2019 at 13:56
TRY_CONVERT might get you halfway there, though it doesn't seem to work for all your date patterns. — Tim Biegeleisen
– Tim Biegeleisen, Commented Mar 6, 2019 at 13:58
@jarlh clearly '10112012' is the 12th day, of the 20th month, in the 1011th year. :) What else could it be? — Thom A
– Thom A ♦, Commented Mar 6, 2019 at 13:59
The long term solution is to fix your data at source and not have datetime values represented as strings. — DavidG
– DavidG, Commented Mar 6, 2019 at 13:59

Thom A · Accepted Answer · 2019-03-06 14:47:49Z

3

This works for all the examples you've given us, and I've had to utterly guess your design, as you haven't posted that DDL and DML I requested. The CONVERT (Transact-SQL) documentation is your friend here, so if you have more styles you haven't supplied, I suggest using that page as a guide to get the results you need.

CREATE TABLE dbo.StringDates (NotDate varchar(12),
                              FormatString varchar(12));
GO

INSERT INTO dbo.StringDates (NotDate,
                             FormatString)
VALUES('01122018','ddmmyyyy'),
      ('01132018','mmddyyyy'),
      ('20181802','yyyyddmm'),
      ('20180214','yyyymmdd'),
      ('2018-01-02','yyyy-mm-dd'),
      ('01/02/2018','dd-mm-yyyy'),
      ('010218','ddmmyy');
GO

SELECT CASE FormatString WHEN 'ddmmyyyy' THEN TRY_CONVERT(date, STUFF(STUFF(NotDate,5,0,'/'),3,0,'/'),103)
                         WHEN 'mmddyyyy' THEN TRY_CONVERT(date, STUFF(STUFF(NotDate,5,0,'/'),3,0,'/'),101)
                         WHEN 'yyyyddmm' THEN TRY_CONVERT(date,CONCAT(LEFT(NotDate,4), RIGHT(NotDate,2),SUBSTRING(NotDate, 5,2)),112)
                         WHEN 'yyyymmdd' THEN TRY_CONVERT(date,NotDate,120)
                         WHEN 'yyyy-mm-dd' THEN TRY_CONVERT(date,NotDate,121)
                         WHEN 'dd-mm-yyyy' THEN TRY_CONVERT(date,REPLACE(NotDate,'-','/'),103)
                         WHEN 'ddmmyy' THEN TRY_CONVERT(date, STUFF(STUFF(NotDate,5,0,'/'),3,0,'/'),3)
       END,
       FormatString
FROM dbo.StringDates

There is, however, only one real solution here. Fix that data type:

UPDATE StringDates
SET NotDate = CONVERT(varchar(8),CASE FormatString WHEN 'ddmmyyyy' THEN TRY_CONVERT(date, STUFF(STUFF(NotDate,5,0,'/'),3,0,'/'),103)
                                                   WHEN 'mmddyyyy' THEN TRY_CONVERT(date, STUFF(STUFF(NotDate,5,0,'/'),3,0,'/'),101)
                                                   WHEN 'yyyyddmm' THEN TRY_CONVERT(date,CONCAT(LEFT(NotDate,4), RIGHT(NotDate,2),SUBSTRING(NotDate, 5,2)),112)
                                                   WHEN 'yyyymmdd' THEN TRY_CONVERT(date,NotDate,120)
                                                   WHEN 'yyyy-mm-dd' THEN TRY_CONVERT(date,NotDate,121)
                                                   WHEN 'dd-mm-yyyy' THEN TRY_CONVERT(date,REPLACE(NotDate,'-','/'),103)
                                                   WHEN 'ddmmyy' THEN TRY_CONVERT(date, STUFF(STUFF(NotDate,5,0,'/'),3,0,'/'),3)
                                        END,112);

ALTER TABLE dbo.StringDates ALTER COLUMN NotDate date;

SELECT *
FROM dbo.StringDates;

GO
--clean up
DROP TABLE dbo.StringDates;

edited Mar 6, 2019 at 14:47

answered Mar 6, 2019 at 14:39

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Shahab Haidar Over a year ago

Hi @Larnu Thanks a lot for your reply. It really helps me.. But cant change data type of column, because it is linked with api..that, is why it's data type is nvarchar. basically, customer sends data to API so we have to accept any data and need to change it at our end.

Shahab Haidar Over a year ago

can i create a function for the same to slecet column with function

Thom A Over a year ago

Yes, @ShahabHaidar, you can. I would suggest an inline table-value function.

Shahab Haidar Over a year ago

Thanks a lot @Larnu I have added an image of real data

Thom A Over a year ago

That image looks nothing like the information you have supplied us, @ShahabHaidar. But, images also aren't helpful here. I've made my stab in the dark here, but I'm guessing this doesn't solve the problem you have (but it would solve it based on the information we had before). This should, however, put you on the path to getting the right answer. You're the one that needs to support the solution in the end. Good luck!

Collectives™ on Stack Overflow

Convert string to date in MS SQL Server

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related