问题描述:

In my stored procedure I'm provided with a varchar parameter that looks like the following:

'201503'

Which obviously indicates the 3rd month of 2015. However, I need to select the previous 12 months in the same format from the given parameter, including the parameter itself.

For example, if given '201503'

I need to get the following:

'201503'

'201502'

'201501'

'201412'

'201411'

'201410'

'201409'

'201408'

'201407'

'201406'

'201405'

'201404'

Some help would really be appreciated! :)

网友答案:

Try this:

DECLARE @m VARCHAR(10) = '201503'

SELECT  LEFT(CONVERT(VARCHAR(8), DATEADD(m, -id, @m + '01'), 112), 6) AS result
FROM    ( VALUES ( 0), ( 1), ( 2), ( 3), ( 4), ( 5), ( 6), ( 7), ( 8), ( 9),
        ( 10), ( 11) ) m ( id ) ORDER BY result DESC
网友答案:

This could be working too.

DECLARE @Date VARCHAR(8) = '201503';

SELECT TOP (12) LEFT(CONVERT(varchar, DATEADD(MM, (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) - 1) * -1, CAST(@Date + '01' AS DATETIME2)),112),6)
FROM sys.columns
网友答案:
DECLARE @Param VARCHAR(6) = '201503'


SELECT TOP 12 CONVERT(VARCHAR(6)
                   , DATEADD(MONTH, - number , CAST(@Param + '01' AS DATE))
                   , 112)
FROM (
        SELECT TOP 12 number 
        FROM master..spt_values
        WHERE number between 0 AND 11 
        GROUP BY number
     )t

Result

201503
201502
201501
201412
201411
201410
201409
201408
201407
201406
201405
201404
相关阅读:
Top