How to Convert String to Date in SQL Server?

Question

I have date column in my table as string type example Feb-18. How do I change it to date type as 01-02-2018?

Do you want to change the actual column data type or just convert the string into a date in a SELECT statement? — DavidG
– DavidG, Commented Mar 12, 2020 at 11:24
Storing dates as string, storing 2 digit years, assuming a language - all big problems. — SMor
– SMor, Commented Mar 12, 2020 at 11:51

Yogesh Sharma · Accepted Answer · 2020-03-12 11:19:46Z

6

Use convert() with add one day :

select convert(date, '01-' + 'Feb-18')

edited Mar 12, 2020 at 11:19

answered Mar 12, 2020 at 11:19

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2020-03-12 11:19:17Z

5

SQL Server is pretty good about figuring out dates. But you need a day, so:

select convert(date, '01-' + datecol)

Note: You should be very careful about storing dates as strings. I would recommend that you test the conversion to be sure it works for all values:

select datecol
from t
where try_convert(date, '01-' + datecol) is null and
      datecol is not null;

If this returns any rows, then you have bad dates in your data. Oh, it would have been better to catch these by rejecting the insert/updates in the first place. However, you might be able to figure out how to fix them.

answered Mar 12, 2020 at 11:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Suraj Kumar · Accepted Answer · 2020-03-12 11:33:22Z

0

You can also try the following way.

Select try_cast('01-' + 'Feb-18' as Date) as [String To Date]

answered Mar 12, 2020 at 11:33

Suraj Kumar

5,6748 gold badges24 silver badges45 bronze badges

Collectives™ on Stack Overflow

How to Convert String to Date in SQL Server?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related