-3

Any help would be appreciated. it works for 2/10/18, 3/10/18 but not 4/10/18 This is to get a date range for a report that runs every month on the 10th for all accounts that are 75 day past due and not reported on last month report

DECLARE @TODAYS_DT DATE = '4/10/2018'

DECLARE @START_DT CHAR(10)
DECLARE @END_DT   CHAR(10)

SELECT  
    @START_DT = CONVERT(VARCHAR, DATEPART(MM, DATEADD(MM, -1, DATEADD(DAY, -75, '4/10/2018')))) + '/20/' + CONVERT(VARCHAR, DATEPART(YEAR, DATEADD(DAY, -75, '4/10/2018')))

SELECT  
    @END_DT = CONVERT(VARCHAR, DATEPART(MM, DATEADD(DAY, -75, '4/10/2018'))) + '/20/' + CONVERT(VARCHAR, DATEPART(YEAR, DATEADD(DAY, -75, '4/10/2018')))

SELECT  
    @START_DT, @END_DT
Steve Tucker
  • 1
  • 1
  • 3
    Edit your question and explain what you are *trying* to do. Broken code doesn't really work so well for that purpose. – Gordon Linoff May 25 '18 at 19:22
  • Which result represents your problem? What date do you expect instead? Why do you expect that? What locale is your session using (i.e. is 4/10/2018 meant to be 4 October, or 10 April)? But mostly, as Gordon asks, what exactly are you trying to do? Because there may well be an easier way than the textual manipulation of strings representing dates... – Matt Gibson May 25 '18 at 19:37
  • You post a question with a broken code, without description. How you do expect us to understand what you want to accomplished???? Learn how to ask question!! https://stackoverflow.com/help/how-to-ask – Eric May 25 '18 at 19:42
  • 1
    Why do you think it should be 2017? 4/10/2018 minus 75 days is 1/24/2018. – Shawn May 25 '18 at 19:45
  • And running your code as-is gives `@START_DT = '12/21/2018'`, `@END_DT = '1/20/2018'`. What are you trying to do with these dates? As Matt said, there may be an easier way to do what you're trying to do. – Shawn May 25 '18 at 19:48
  • it should return 12/21/2017 if you run for 3/10/2018 it returns 12/20/17 – Steve Tucker May 25 '18 at 20:10

1 Answers1

0

You need to go further back in time to get to 2017. Try subtracting 100 instead of 75.

DECLARE @TODAYS_DT DATE = '4/10/2018'

DECLARE @START_DT CHAR(10)
DECLARE @END_DT   CHAR(10)

SELECT  
    @START_DT = CONVERT(VARCHAR, DATEPART(MM, DATEADD(MM, -1, DATEADD(DAY, -100, '4/10/2018')))) + '/21/' + CONVERT(VARCHAR, DATEPART(YEAR, DATEADD(DAY, -100, '4/10/2018')))

SELECT  
    @END_DT = CONVERT(VARCHAR, DATEPART(MM, DATEADD(DAY, -100, '4/10/2018'))) + '/20/' + CONVERT(VARCHAR, DATEPART(YEAR, DATEADD(DAY, -100, '4/10/2018')))

SELECT  
    @START_DT, @END_DT
Daniel Marcus
  • 2,686
  • 1
  • 7
  • 13