SQLTeam.com | Weblogs | Forums

Int to year and month


#1

Hi all,

How do I convert int of 201405 to year and month as 2015-05?

The field called CM.

Select CM
From CMTbl

Result set:
201405
201501
201502
201503

Need the result set to (year and month):
2014-05
2015-01
2015-02
2015-03

Thanks all


#2

SELECT CM / 100 AS [Year], CM % 100 AS [Month]

if you want it as string '2015-05', convert the above [Year] and [Month] to string and concatenate it


#3

If you want a field year and a field month the @khtan has a solution.
If you want a character field then you could use something like
SELECT STUFF(CAST(CM AS CHAR(6) ), 5, 0, '-') FROM CMTbl;


#4

SELECT STUFF(CM, 5, 0, '-')
FROM (VALUES(201405),(201501),(201502),(201503)) AS x(CM)


#5

Thanks all