SQL根据日期选择前12个月 [英] SQL Select previous 12 months based on date
问题描述
我正在尝试获取前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
< blockquote>问题出在
+ 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
< span class =code-keyword> 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
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
< span class =code-keyword> 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 TINYINT
SET @Today = GETDATE()
SET @nMonths = 12
SELECT 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 EndDateOP
FROM ( SELECT TOP ( @nMonths )n = ROW_NUMBER() OVER ( ORDER BY NAME )
FROM master.dbo.syscolumns ) n
ORDER 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 onDECLARE @Today DATETIME, @nMonths TINYINT SET @nMonths = 12 SET @Today = DATEADD(month, (-1) * @nMonths, GETDATE()) ;WITH q AS ( SELECT @Today AS datum UNION ALL SELECT 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 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' and number between 1 and @nMonths
DECLARE @Today DATETIME , @nMONTHs TINYINT, @LinNum int = 100 SET @Today = DATEADD(MONTH, -12, GETDATE()) SET @nMONTHs = 12 SELECT n.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)) Name FROM ( SELECT TOP ( @nMONTHs )n = ROW_NUMBER() OVER ( ORDER BY NAME ) FROM master.dbo.syscolumns ) n ORDER BY n.n
这篇关于SQL根据日期选择前12个月的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!