How to convert string date into valid date format in SQL Server 2008?

Question

I have string date in yymmdd format, for example 150202

I want to convert this string into a valid date in format yyyymmdd, e.g. 20150202.

Thanks in advance.

Have you made some research before asking? Tested some code? This is a really common question and you have lot of samples here in SO. — Yaroslav
– Yaroslav, Commented Feb 13, 2015 at 7:28
SQL Server DATE or DATETIME2(n) don't have any format associated with them - they're stored in binary bytes — marc_s
– marc_s, Commented Feb 13, 2015 at 7:30
Stroing date's as string is a bad practice .. you can read more here:sqlblog.org/2009/10/12/… — Deepshikha
– Deepshikha, Commented Feb 13, 2015 at 7:33
I agree with @Deepshikha. If you known that 150202 means 20150202 just concat "20" and "150202" and than convert this string into date format — Kotu
– Kotu, Commented Feb 13, 2015 at 7:39

Felix Pamittan · Accepted Answer · 2015-02-13 07:43:35Z

2

convert your string to datetime and then do that you want with it

declare @dt varchar(6) = '150213'
select CONVERT(datetime, @dt, 112)

Do another CONVERT to transform it to yyyymmdd format.

SELECT CONVERT(VARCHAR(10), CONVERT(DATETIME, @dt, 112), 112)

edited Feb 13, 2015 at 7:43

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

answered Feb 13, 2015 at 7:30

ASh

35.9k9 gold badges68 silver badges89 bronze badges

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

7 Comments

Felix Pamittan Over a year ago

Did you test it? The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

marc_s Over a year ago

@wewesthemenace: works just fine for me (SQL Server 2014 - US English language)

ASh Over a year ago

@wewesthemenace, i improved my answer, i used 'yyddmm' format for string. it is 'yymmdd' now

Felix Pamittan Over a year ago

@marc_s, it's 151302 before the edit. @Ash, thanks!

ASh Over a year ago

@mohan111, it is coming as varchar and output is datetime, which can be displayed in many formats

|

I A Khan · Accepted Answer · 2015-02-13 07:40:00Z

0

may this will work

select CONVERT(datetime, '150202', 112)

for all date conversions

http://www.sqlusa.com/bestpractices/datetimeconversion/

answered Feb 13, 2015 at 7:40

I A Khan

8,92716 gold badges57 silver badges78 bronze badges

Comments

Shajeer Puzhakkal · Accepted Answer · 2015-02-17 05:21:46Z

0

instead of select CONVERT(datetime, '150202', 112) its better to use "select TRY_CONVERT(datetime, '150202', 112)" while using try_convert if there is any error it will returns null,if we are using convert it will returns error when conversion fails.

answered Feb 17, 2015 at 5:21

Shajeer Puzhakkal

1613 silver badges13 bronze badges

Collectives™ on Stack Overflow

How to convert string date into valid date format in SQL Server 2008?

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related