Select specific dates SQL

Question

I have a report that I should run every month, and select data from 21st of the same month of last year till 20th of the current month, current year. i.e. it's August 29 now and today I need to run my report, but I want it to show only data from 08.21.2016-08.20.2017 and when I run it on September I want data from 09.20.2016-09.21.2017 ...

I was using

Select *
from invt
where DATE > (GETDATE()-365)

but it it's approximate, and I have to run report exactly on 20th of each month.

Any idea how to do it ? TIA

probable duplicate of stackoverflow.com/questions/37575216/… — Ferdinand Gaspar
– Ferdinand Gaspar, Commented Aug 29, 2017 at 20:17

Esteban P. · Accepted Answer · 2017-08-29 20:30:00Z

2

Should work with:

SELECT *
  FROM invt
 WHERE DATE BETWEEN 
       DATEADD(YEAR,-1,DATEADD(DAY,19,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)))
   AND DATEADD(DAY,20, DATEADD(MONTH,DATEDIFF(MONTH,0, GETDATE()),0))

Looks scary, but its basically doing simple things:

For the 20th of last year:

Get current date
just take the month (beginning of month)
add 19 days (now we are on 20th of current month)
substract one year

Pretty the same logic for the 21st of current month. just without substracting one year.

edited Aug 29, 2017 at 20:30

answered Aug 29, 2017 at 20:24

Esteban P.

2,8203 gold badges31 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Monika Over a year ago

oh, great! This is what I needed. Thx for detailed explanation.

John Cappelletti · Accepted Answer · 2017-08-29 20:28:00Z

1

Another option if 2012+

Where Date between format(DateAdd(YEAR,-1,GetDate()),'yyyy-MM-21')
               and format(GetDate(),'yyyy-MM-20')

The date would be 2016-08-21 and 2017-08-20

answered Aug 29, 2017 at 20:28

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

3 Comments

Jason A. Long Over a year ago

You should avoid use of the FORMAT function. There's been a fair bit of testing and performs, horribly compared to CAST or CONVERT.

John Cappelletti Over a year ago

@JasonA.Long Fully aware of the performance of Format(). HOWEVER this will be called/resolved ONCE not record by record.

Jason A. Long Over a year ago

Fair enough... But, will the OP be aware of this the next time when they need to do this in a correlated sub-query or cross apply?

Jason A. Long · Accepted Answer · 2017-08-29 20:28:58Z

1

If you're using SQL Server 2012 or later, you can use the DATEFROMPARTS function...

SELECT 
    *
FROM
    dbo.MyTable mt
WHERE 
    mt.SomeDate >= DATEFROMPARTS(YEAR(GETDATE()) -1, MONTH(GETDATE()), 20);

answered Aug 29, 2017 at 20:28

Jason A. Long

4,4421 gold badge14 silver badges18 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2017-08-29 20:24:29Z

0

You can get the 21st of the current month using:

dateadd(day, 21 + 1 - day(getdate()), cast(getdate() as date))

If you want from one year ago:

where date < dateadd(day, 21 + 1 - day(getdate()), cast(getdate() as date)) and
      date >= dateadd(year, 1, dateadd(day, 21 + 1 - day(getdate()), cast(getdate() as date)))

answered Aug 29, 2017 at 20:24

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

JamieD77 · Accepted Answer · 2017-08-29 20:37:28Z

0

DECLARE @CurrentDate datetime = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) + 19
DECLARE @PriorDate datetime = DATEADD(YEAR, -1, @CurrentDate) + 1

SELECT @CurrentDate, @PriorDate

answered Aug 29, 2017 at 20:37

JamieD77

14k1 gold badge19 silver badges27 bronze badges

Collectives™ on Stack Overflow

Select specific dates SQL

5 Answers 5

1 Comment

3 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related