0

I have string 20120821 and I need to convert it into 21 AUG 2012 string also. What is easy way?

gorrch
  • 521
  • 3
  • 16

5 Answers5

2

There's a big list of the different outputs that you can find here.

But to answer your questions:

DECLARE @date VARCHAR(8) = '20120821';

SELECT CONVERT(VARCHAR(11), CONVERT(DATE, @date), 113);
Jim Jimson
  • 2,368
  • 3
  • 17
  • 40
2
declare @t varchar(50) = '20120821'
select convert(varchar(50),convert(date,@t),113)
Red Devil
  • 2,343
  • 2
  • 21
  • 41
1

You can convert to a date using datefromparts():

select datefromparts(right(ddmmyyyy, 4), substring(ddmmyyyy, 3, 2), left(ddmmyyyy, 2))

Then you can convert to a string:

select convert(varchar(255), datefromparts(right(ddmmyyyy, 4), substring(ddmmyyyy, 3, 2), left(ddmmyyyy, 2)), 106)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

Use cast and convert like:

select convert(varchar, cast('20120821' as datetime), 6)

or

select convert(varchar, cast('20120821' as datetime), 113)
apomene
  • 14,282
  • 9
  • 46
  • 72
0

Tough this question already have four answers - none of the existing answers is much more than a code dump - so I figured I would add a little explanation.

String representations of dates are considered notorious because a lot of them are ambiguous - For example, 02/04/2019 can be interpreted as either February 4th 2019 or as April 2nd 2019.

It gets worst when you have a two digit year - 01/02/03 can represent so many different dates - including different centuries!

Lucky for you, your source string is in one of the two formats that are guaranteed to always be interpreted correctly by SQL Server when converting to Date (but not to DateTime!):
yyyymmdd is one and yyyy-mm-dd is the other.

Both of these formats are international standard and described in ISO 8601.

Please note, however, that there is a known bug (feature?) in the DateTime data type that makes the yyyy-mm-dd format culture dependent. This bug does not exists in the new and improved alternative data type called DateTime2.

I will not add a code to this answer since it would just be repeating the code from Jim Jimson's answer or from apomene's answer.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • Glad you mentioned the issues with 'yyyy-mm-dd'. When working with dates one should always use 'yyyymmdd' just to be on the safe side. – Jim Jimson Jul 27 '19 at 05:50
  • @jimjimson that's correct when working with `DateTime`, but none of the other date and time data type has this problem - so as long as you are not using `DateTime` you're safe with the more human readable format of `yyyy-mm-dd`. – Zohar Peled Jul 27 '19 at 07:20