CODEWITHSUNDEEP
sqlsql-server
Rhyfelwr
Rhyfelwr

Reputation: 329

Get records between 24th of last month and 23rd of current month

I know next to nothing about SQL I am trying to make an SQL Query in Microsoft SQL Server Management Studio where the records are received from within a dynamic period (24th of last month - 23rd of this month). Currently I need to edit the dates manually or add them in via an excel VBA macro where the two points in time are declared as two variants. I've been looking around the internet, trying a few things like:

WHERE DATEPART(m,day_id)=DATEPART(m,DATEADD(m,-1,getdate()))
AND DATEPART (yyyy,day_id)=DATEPART(yyyy,DATEADD(m,-1,getdate()))

but this doesn't count for 24th-23rd and gives a syntax error.

Here's my code where I need to manually edit dates: 

SELECT
  reviewer_name,
  CAST(ddd_id AS date) AS day_id,
  report_id,
  report_name,
  amount_events,
  group_name,
  percent_checked_events,
  comment AS SPL_comment
FROM reports_history_GA
WHERE (closed != 1
   AND ddd_id > '2017-01-01'
   AND [percent_checked_events] != '100'
   AND report_name NOT LIKE '%ther_jo%'
   AND report_name NOT LIKE 'QATeam'
   AND comment NOT LIKE '%escal%')
OR    (ddd_id <= '2018-02-23'
   AND check_start_date > '2018-02-23'
   AND comment NOT LIKE '%escal%')
ORDER BY dd_id ASC

Upvotes: 0

Views: 368

Answers (3)

Michał Turczyn
Michał Turczyn

Reputation: 37347

Here's alternative to accepted answer:

select cast(dateadd(month,-1,dateadd(day,24 - datepart(day,getdate()),getdate())) as date) [24th of previous month],
       cast(dateadd(day,23 - datepart(day,getdate()),getdate()) as date) [23rd of current month]

Upvotes: 1

Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6088

SELECT CAST('24 '+ RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,-1,GETDATE()),113),8) AS date) AS LastMonth,
CAST('23 '+ RIGHT(CONVERT(CHAR(11),GETDATE(),113),8) AS date) AS CurrentMonth

Output:

LastMonth   CurrentMonth
2018-02-24  2018-03-23

Upvotes: 1

Squirrel
Squirrel

Reputation: 24763

this will gives you the dates

select  [24th of last month]    = dateadd(month, datediff(month, 0, getdate()) - 1, 23),
        [23rd of current month] = dateadd(month, datediff(month, 0, getdate()),     22)

Upvotes: 4

Related Questions