代码之家 › 专栏 › 技术社区 › ASH

获取动态上个月结束日期并插入

tsql sql-server-2008 sql-server

-1

ASH · 技术社区 · 7 年前

我正在尝试动态获取上个月的最后一天,并将其传递到查询中。我尝试了下面的想法,但没用。

DECLARE @PrevMonthLastDay DATE=(SELECT CONVERT(CHAR(15),DATEADD(DD,-Day(GETDATE()),GETDATE()),106))
--PRINT @PrevMonthLastDay
INSERT INTO [TBL_ParseRawDataHist]  
SELECT   [SrcID],[ASOFDATE],
         dbo.SplitIndex('|', LTRIM(RTRIM([SrcID])), 1) AS Parse1, 
         dbo.SplitIndex('|', LTRIM(RTRIM([SrcID])), 2) AS Parse2,
         dbo.SplitIndex('|', LTRIM(RTRIM([SrcID])), 3) AS Parse3, 
         dbo.SplitIndex('|', LTRIM(RTRIM([SrcID])), 4) AS Parse4, 
         dbo.SplitIndex('|', LTRIM(RTRIM([SrcID])), 5) AS Parse5, 
         dbo.SplitIndex('|', LTRIM(RTRIM([SrcID])), 6) AS Parse6, 
         dbo.SplitIndex('|', LTRIM(RTRIM([SrcID])), 7) AS Parse7, 
         dbo.SplitIndex('|', LTRIM(RTRIM([SrcID])), 8) AS Parse8,
         dbo.SplitIndex('|', LTRIM(RTRIM([SrcID])), 9) AS Parse9, 
         dbo.SplitIndex('|', LTRIM(RTRIM([SrcID])), 10) AS Parse10          
FROM     TBL_FR2052A_RAW_DATA_HIST  
--WHERE  ASOFDATE = '02/28/2018'
WHERE    ASOFDATE = ''' + CAST(@PrevMonthLastDay AS varchar(15)) + '''

结果:从字符串转换日期和/或时间时,转换失败。

我做错了什么?我正在使用SQL Server 2008!

4 回复 | 直到 7 年前

Zohar Peled 7 年前

您不需要转换为字符串,只需使用 dateadd 和 day :

DECLARE @CurrentDate as date = GETDATE()

SELECT  @CurrentDate As CurrentDate, 
        DATEADD(DAY, -DAY(@CurrentDate), @CurrentDate) As EndOfLastMonth

结果:

CurrentDate     EndOfLastMonth
06.03.2018      28.02.2018

2012版引入了 EOMONTH() 内置函数,因此如果升级SQL Server,可以执行此操作以获得相同的结果: select eomonth(dateadd(month,-1,getdate()))

使现代化
以下是完整的insert语句:

DECLARE @CurrentDate as date = GETDATE()

INSERT INTO [TBL_ParseRawDataHist]  
SELECT   [SrcID],[ASOFDATE],
         dbo.SplitIndex('|', LTRIM(RTRIM([SrcID])), 1) AS Parse1, 
         dbo.SplitIndex('|', LTRIM(RTRIM([SrcID])), 2) AS Parse2,
         dbo.SplitIndex('|', LTRIM(RTRIM([SrcID])), 3) AS Parse3, 
         dbo.SplitIndex('|', LTRIM(RTRIM([SrcID])), 4) AS Parse4, 
         dbo.SplitIndex('|', LTRIM(RTRIM([SrcID])), 5) AS Parse5, 
         dbo.SplitIndex('|', LTRIM(RTRIM([SrcID])), 6) AS Parse6, 
         dbo.SplitIndex('|', LTRIM(RTRIM([SrcID])), 7) AS Parse7, 
         dbo.SplitIndex('|', LTRIM(RTRIM([SrcID])), 8) AS Parse8,
         dbo.SplitIndex('|', LTRIM(RTRIM([SrcID])), 9) AS Parse9, 
         dbo.SplitIndex('|', LTRIM(RTRIM([SrcID])), 10) AS Parse10          
FROM     TBL_FR2052A_RAW_DATA_HIST  
WHERE    ASOFDATE = DATEADD(DAY, -DAY(@CurrentDate), @CurrentDate)

B3S 7 年前

每月的第一天和最后一天:

DECLARE @PrevMonthFirstDay DATE= DATEADD(month, DATEDIFF(month, 0, getdate()), 0)

DECLARE @PrevMonthLastDay DATE= DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))

Tab Alleman 7 年前

不能将字符串与 DATE ,必须将这两个字符串都转换为字符串:

WHERE    ASOFDATE = ''' + CAST(@PrevMonthLastDay AS varchar(15)) + ''''

ASH 7 年前

Zohar,你更新的代码给了我一个尝试的想法。最终,这对我起了作用。

DECLARE @PrevMonthLastDay DATE=(SELECT CONVERT(CHAR(15),DATEADD(DD,-Day(GETDATE()),GETDATE()),106))
Print @PrevMonthLastDay

INSERT   INTO [TBL_ParseRawDataHist]  
SELECT   [SrcID],[ASOFDATE],
         dbo.SplitIndex('|', LTRIM(RTRIM([SrcID])), 1) AS Parse1, 
         dbo.SplitIndex('|', LTRIM(RTRIM([SrcID])), 2) AS Parse2,
         dbo.SplitIndex('|', LTRIM(RTRIM([SrcID])), 3) AS Parse3, 
         dbo.SplitIndex('|', LTRIM(RTRIM([SrcID])), 4) AS Parse4, 
         dbo.SplitIndex('|', LTRIM(RTRIM([SrcID])), 5) AS Parse5, 
         dbo.SplitIndex('|', LTRIM(RTRIM([SrcID])), 6) AS Parse6, 
         dbo.SplitIndex('|', LTRIM(RTRIM([SrcID])), 7) AS Parse7, 
         dbo.SplitIndex('|', LTRIM(RTRIM([SrcID])), 8) AS Parse8,
         dbo.SplitIndex('|', LTRIM(RTRIM([SrcID])), 9) AS Parse9, 
         dbo.SplitIndex('|', LTRIM(RTRIM([SrcID])), 10) AS Parse10          
FROM     TBL_FR2052A_RAW_DATA_HIST  
WHERE    ASOFDATE = @PrevMonthLastDay

谢谢