Clever Geek Handbook

How to convert yyyymm to Mon-yyyy?

I have a YearMonth column with a value like (201208). I would like to convert the entire column to Aug-2012 (Mon-Year) format. I did it.

select Convert(varchar(11),Cast(right(yearmonth,2)+'-'+left(yearmonth,4) as varchar (11)),0)
from report.TEST

This code changed from 2012 to 08-2012, but then I was not able to change it until August 2012. Any suggestions. Thanks to

+3


source to share


4 answers


Actually, there is a format function for SQL Server 2012 :



SELECT FORMAT(CONVERT(DATE,yearmonth+'01'), N'MMM-yyyy', 'en-US') AS YearMonth;
+4


source


For MS-SQL

DECLARE @d1 INT; SET @d1 = 201208
DECLARE @d2 VARCHAR(6); SET @d2 = 201208


SELECT 'data type'='int'
, 'in'=@d1
, 'out'=CONVERT(VARCHAR(3), CAST(LEFT(@d1,6)+'01' AS DATE), 107) 
  + '-' + LEFT(@d1,4)

SELECT 'data type'='varchar'
, 'in'=@d2
, 'out'=CONVERT(VARCHAR(3), CAST(@d2+'01' AS DATE), 107) 
  + '-' +LEFT(@d2,4)


/* -- OUTPUT
data type   in      out
int         201208  Aug-2012

data type   in      out
varchar     201208  Aug-2012
*/

You can also use DATENAME



SELECT 'data type' = 'int'
, 'in' = @d1
, 'out' = LEFT(DATENAME(month, CAST(LEFT(@d1,6)+'01' AS DATE)), 3) 
  + '-' + LEFT(@d1,4)

SELECT 'data type' = 'varchar'
, 'in' = @d2
, 'out' = LEFT(DATENAME(month, CAST(@d2+'01' AS DATE)), 3)
  + '-' + LEFT(@d2,4)

Or use different STUFF :)

DECLARE @d1 INT; SET @d1 = 201208
DECLARE @d2 VARCHAR(6); SET @d2 = 201208


SELECT 'data type' = 'int'
, 'in' = @d1
, 'out' = STUFF(CONVERT(VARCHAR(20), CAST(LEFT(@d1,6)+'01' AS DATE), 107), 4, 5, '-')

SELECT 'data type' = 'varchar'
, 'in' = @d2
, 'out' = STUFF(CONVERT(VARCHAR(20), CAST(@d2+'01' AS DATE), 107), 4, 5, '-')
+1


source


You will need to turn 08-2012 into a valid day and time, but that does the trick.

SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mon-YYYY]

Source: http://www.sql-server-helper.com/tips/date-formats.aspx

0


source


select Substring(Convert(VarChar(11),Convert(DateTime,SomeMinthYear) + '01',113),4,8)

would be one way. for example convert the date to one of the formatted strings using mon yyyy in the format and then cut it out.

0


source






More articles:


All Articles