本文介绍了SQL根据日期选择前12个月的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 29岁程序员,3月因学历无情被辞! 我正在尝试获取前12个月的列表,其中任何日期输入格式为varchar so(201404将是2014年4月),并将返回所有月份的列表,直至201305. 目前我有以下但是它带来了以下12个月,我需要前一个12个月的格式201404. DECLARE @ Today DATETIME , @ nMonths TINYINT SET @ Today = GETDATE() SET @nMonths = 12 SELECT SUBSTRING( DATENAME(月,日DADADD(dd,-1,DATEADD(月,nn + DATEDIFF(月, 0 , @ Today ), 0 ))), 1 , 3 ) + CAST(年份(DATEADD(dd,-1,DATEADD(月,nn + DATEDIFF(月, 0 , @ Today )-1, 0 ))) AS VARCHAR ( 4 )) AS EndDateOP FROM ( SELECT TOP ( @ nMonths )n = ROW_NUMBER() OVER ( ORDER BY NAME) FROM master.dbo.syscolumns)n ORDER BY 1 DESC 解决方案 问题出在 + CAST(YEAR(DATEADD(dd,-1,DATEADD(月,nn + DATEDIFF(月, 0 , @ Today )-1, 0 ))) AS VARCHAR ( 4 )) - 请参阅 -1 ...你在序列中查看上个月的年份 - 它应该是 + CAST(年份(DATEADD(dd) ,-1,DATEADD(月,nn + DATEDIFF(月, 0 , @ Today ), 0 ))) AS VARCHAR ( 4 )) 这是生成相同序列的另一种选择 - 它会稍微多一点高性能,因为格式较少 DECLARE @ Today DATETIME , @ nMonths TINYINT SET @ nMonths = 12 SET @ Today = DATEADD(月,( - 1)* @nMonths ,GETDATE()) ; WITH q AS ( SELECT @ Today AS datum UNION ALL SELECT DATEADD(月, 1 ,基准) FROM q WHERE datum + 1 < GETDATE()) SELECT SUBSTRING(DATENAME(MONTH,datum), 1 , 3 )+ CAST(YEAR(datum) AS VARCHAR ( 4 )) FROM q 摘自Manas在在SQL中生成序列的工作[ ^ ] DECLARE @ Today DATETIME , @ nMonths TINYINT SET @ nMonths = 12 SET @ Today = GETDATE() SELECT RIGHT ( CONVERT ( VARCHAR ,DATEADD(MM,-1 * number, @ Today ), 106 ), 8 ) FROM master.dbo.spt_values WHERE TYPE = ' P' 和编号 1 和 @ nMonths DECLARE @Today DATETIME ,@ nMONTHs TINYINT,@ Linnum int = 100 SET @Today = DATEADD(MONTH,-12,GETDA TE()) SET @nMONTHs = 12 SELECT nn ID, DATENAME(MONTH,DATEADD(dd, - (DAY(GETDATE())+ 1),DATEADD(月,nn + DATEDIFF(月,0,@今天),0)))+'' + CAST(年份(DATEADD( - Dd, - (DAY(GETDATE())+ 1) ,DATEADD(月,nn + DATEDIFF(月,0,@今天),0)))AS VARCHAR(4))名称 FROM(SELECT TOP(@nMONTHs)n = ROW_NUMBER()OVER(按名称排序) )来自master.dbo.syscolumns)n ORDER BY nn I am trying to get a list of the previous 12 months with any date input with the format as varchar so (201404 would be April 2014) and would return a list of all months up to 201305.At the moment I have the following but it brings thefollowing 12 months, I need the previous 12 months in format 201404.DECLARE @Today DATETIME , @nMonths TINYINTSET @Today = GETDATE()SET @nMonths = 12SELECT SUBSTRING(DATENAME(MONTH, DATEADD(dd, -1, DATEADD(month, n.n + DATEDIFF(month, 0, @Today), 0))), 1, 3)+ CAST(YEAR(DATEADD(dd, -1, DATEADD(month , n.n + DATEDIFF(month, 0, @Today) -1 , 0))) AS VARCHAR(4))AS EndDateOPFROM ( SELECT TOP ( @nMonths )n = ROW_NUMBER() OVER ( ORDER BY NAME ) FROM master.dbo.syscolumns ) nORDER BY 1 DESC 解决方案 The problem is in + CAST(YEAR(DATEADD(dd, -1, DATEADD(month , n.n + DATEDIFF(month, 0, @Today) -1 , 0))) AS VARCHAR(4)) - see the -1 ... you're looking at the year of the previous month in the sequence - it should be + CAST(YEAR(DATEADD(dd, -1, DATEADD(month , n.n + DATEDIFF(month, 0, @Today), 0))) AS VARCHAR(4))Here is another alternative for generating the same sequence - it will be slightly more performant as there is less formatting going on DECLARE @Today DATETIME, @nMonths TINYINTSET @nMonths = 12SET @Today = DATEADD(month, (-1) * @nMonths, GETDATE());WITH q AS(SELECT @Today AS datumUNION ALLSELECT DATEADD(month, 1, datum)FROM q WHERE datum + 1 < GETDATE())SELECT SUBSTRING(DATENAME(MONTH, datum), 1, 3) + CAST(YEAR(datum) AS VARCHAR(4))FROM qtaken from Manas' work at Generating a Sequence in SQL[^]DECLARE @Today DATETIME, @nMonths TINYINTSET @nMonths = 12SET @Today = GETDATE()SELECTRIGHT(CONVERT(VARCHAR,DATEADD(MM, -1*number, @Today),106),8)FROMmaster.dbo.spt_valuesWHERE TYPE = 'P' and number between 1 and @nMonthsDECLARE @Today DATETIME , @nMONTHs TINYINT, @LinNum int = 100SET @Today = DATEADD(MONTH, -12, GETDATE())SET @nMONTHs = 12SELECTn.n ID,DATENAME(MONTH, DATEADD(dd, -(DAY(GETDATE())+1), DATEADD(MONTH, n.n + DATEDIFF(MONTH, 0, @Today), 0))) + ' '+ CAST(YEAR(DATEADD(dd, -(DAY(GETDATE())+1), DATEADD(MONTH , n.n + DATEDIFF(MONTH, 0, @Today) , 0))) AS VARCHAR(4))NameFROM ( SELECT TOP ( @nMONTHs )n = ROW_NUMBER() OVER ( ORDER BY NAME ) FROM master.dbo.syscolumns ) nORDER BY n.n 这篇关于SQL根据日期选择前12个月的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!