Get Dynamic Prior Month-End Date and Insert Into

Question

I am trying to dynamically get the last day of the prior month and pass it into a query. I tried the following idea, but it dind't work.

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)) + '''

Result: Conversion failed when converting date and/or time from character string.

What am I doing wrong here? I am using SQL Server 2008!

Possible duplicate of [How to get first and last day of previous month (with timestamp) in SQL Server](https://stackoverflow.com/questions/11743810/how-to-get-first-and-last-day-of-previous-month-with-timestamp-in-sql-server) — Tab Alleman, Mar 06 '18 at 16:55

Zohar Peled · Accepted Answer · 2018-03-07T14:39:37.363

You don't need to convert to string, simply use dateadd and day:

DECLARE @CurrentDate as date = GETDATE()

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

Results:

CurrentDate     EndOfLastMonth
06.03.2018      28.02.2018

The 2012 version introduced the EOMONTH() built in function, so if you upgrade your SQL Server you can do this to get the same result: select eomonth(dateadd(month,-1,getdate()))

Update
Here is the complete insert statement:

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)

Thanks. How do I pass that into my INSERT INTO query? I tried your suggestion and nothing happens. — ASH, Mar 06 '18 at 16:28
Somehting is still not quite right. I just updated my original post. This is where I am now. Any more thoughts on what could be wrong? I'm sure this is doable. — ASH, Mar 06 '18 at 19:23

score 1 · Answer 2 · answered Mar 06 '18 at 16:28

1

first and last day of month:

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

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

answered Mar 06 '18 at 16:28

B3S

1,021
7
18

score 1 · Answer 3 · answered Mar 06 '18 at 16:58

1

You can't concatenate a string with a DATE, you have to CAST both as strings:

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

answered Mar 06 '18 at 16:58

Tab Alleman

31,483
7
36
52

score 0 · Answer 4 · answered Mar 07 '18 at 15:11

Zohar, your updated code gave me an idea to try. Ultimately, this worked for me.

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

Thanks!!

Get Dynamic Prior Month-End Date and Insert Into

4 Answers4