May 31, 2020 at 3:14 pm
BI have a requirement to write a query to get all the records between 21st of previous month to 20th of next month. So for eg: if the current date is 18-May, I should get records from 21- Apr to 20-May. This will go on till 20th May. Now from 21st May to 20-June, I should get records only from 21-May.
So while execute the procedure, how should I pass this date parameters? This is not passing from UI. Procedure has no input parameters. It should calculate based on the current date. In simple terms, get all records from 21st of Previous month to 20th of Current / Next month.
i tried the below code when I googled it, but not working properly, means if I execute on last date (30/31st) of the month, say on 31st May, it is getting records from Apr 21 to May 21 which should not be the case. It should get record from 21st of May.
Declare @PrevMonthDt DATETIME
Declare @CurrMonthDt DATETIME
Select @PrevMonthDt = DATEADD(MONTH,DATEDIFF(MONTH, '17530101', GETUTCDATE())-1, '17530121')
Select @CurrMonthDt= DATEADD(MONTH,DATEDIFF(MONTH, '17530101', GETUTCDATE()), '17530121')
Please provide any clue.
May 31, 2020 at 5:40 pm
-- *** Test Data ***
CREATE TABLE #t
(
YourDateStamp datetime NOT NULL PRIMARY KEY
);
INSERT INTO #t
VALUES ('20200418'),('20200425'),('20200518'),('20200525'),('20200618'),('20200625');
-- *** End Test Data ***
SELECT T.YourDateStamp
FROM #t T
CROSS APPLY
(
VALUES( DATEADD(day, 20, DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP - 20), 0)) )
) X (StartDate)
WHERE T.YourDateStamp >= X.StartDate
AND T.YourDateStamp < DATEADD(month, 1, X.StartDate);
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply