SQL that outputs the last July 1 that is less than two years away

Question

So I'm trying to correct some SQL that's attempting to output the last July 1 that is less than two years away from a certain date. So 06/30/2014 would return 07/01/2015 since 07/01/2016 is more than two years in the future. The date 07/02/2014 should return 07/01/2016, but the SQL below is returning 07/01/2015 incorrectly.

DECLARE @Year VARCHAR (10) 
DECLARE @Date VARCHAR(10)
DECLARE @OrigDate VARCHAR (10) = /*substitution for relevant date here*/

SET @Year=(DATEPART (YYYY,DATEADD(YYYY,2,GETDATE()))) 
SET @Date = (SELECT '07/01/'+@Year)

IF @OrigDate > DATEADD(YEAR,-2,GETDATE()) 
 BEGIN 
    SET @Year=(DATEPART (YYYY,DATEADD(YYYY,1,GETDATE()))) 
    SET @Date = (SELECT '07/01/'+@Year)
 END

SELECT @Date AS RetValue

Now that we're in August, it's firing correctly, but I need to fix it before next July rolls around. (The date isn't always the current date, but it's usually close)

do you use sql server 2012 or later? why are you using varchar as a date? — Paul Maxwell, Aug 08 '14 at 01:16

score 0 · Answer 1 · answered Aug 08 '14 at 00:48

As far as I am aware, this is cannot be done in a standard way and is dependent on the SQL flavour you are using. Your example looks like MS SQL server, in which case my answer won't be overly helpful. But in postgres:

SELECT s FROM generate_series('2014-07-02 00:00'::timestamp,
                              ('2014-07-02 00:00'::timestamp +
                              (interval '2 years' - interval '1 day')) , '1 day') as s 
where date_part('day', s) = 1 and date_part('month', s) = 7 order by s DESC LIMIT 1;

score 0 · Accepted Answer · edited May 23 '17 at 10:26

And the SQL server version of my postgres answer. Credit to https://stackoverflow.com/a/7149386/13822

WITH cSequence AS
(
  SELECT
    cast('2014-01-01 00:00' as datetime ) AS StartRange,
    DATEADD(DAY, 1, cast('2014-01-01 00:00' as datetime )) AS EndRange
  UNION ALL
  SELECT
    EndRange,
    DATEADD(DAY, 1, EndRange)
  FROM cSequence
  WHERE DATEADD(DAY, 1, EndRange) < DATEADD(year, 2, '2014-01-01 00:00')
)
SELECT top 1 StartRange FROM cSequence where datepart(month, startRange) = 7 and datepart(day, StartRange) = 1
ORDER BY StartRange DESC
OPTION (MAXRECURSION 0)  ;

Paul Maxwell · Answer 3 · 2014-08-08T01:56:30.967

for sql server 2012+ deduct 6 months from the given date, take the year of that and add 2 years, convert that year with month 7 day 1

DECLARE @OrigDate date = '20140630' -- NB! safest YYYYMMDD format

select
datefromparts(year(dateadd(year,2,dateadd(month,-6,@OrigDate))),7,1)
;

DECLARE @OrigDate date = '20140702' -- NB! safest YYYYMMDD format

select
datefromparts(year(dateadd(year,2,dateadd(month,-6,@OrigDate))),7,1)
;

see: http://sqlfiddle.com/#!6/d41d8/20720

Continuing on, in earlier sql server versions, we can still deduct 6 months from "the date" and add 2 years but it gets a little more complex as we now need to use more dateadd() calculations. The zero date in sql server is 1900-01-01, so if you add 6 months to it you get 1900-07-01. Then add the required number of years to reach 2015 or 2016 etc to 1900-07-01 you get to July 1 of the required year.

select
      the_date
    , datefromparts(year(dateadd(year,2,dateadd(month,-6,the_date))),7,1)                    AS by_dateparts
    , dateadd(year,year(dateadd(year,2,dateadd(month,-6,the_date)))-1900,dateadd(month,6,0)) AS by_dateadds
from table1
;

Here are some tests (based on a small table in lieu of using an @ parameter)

|   THE_DATE | BY_DATEPARTS |                 BY_DATEADDS |
|------------|--------------|-----------------------------|
| 2014-06-24 |   2015-07-01 | July, 01 2015 00:00:00+0000 |
| 2014-06-30 |   2015-07-01 | July, 01 2015 00:00:00+0000 |
| 2014-07-06 |   2016-07-01 | July, 01 2016 00:00:00+0000 |
| 2014-07-12 |   2016-07-01 | July, 01 2016 00:00:00+0000 |
| 2014-07-18 |   2016-07-01 | July, 01 2016 00:00:00+0000 |
| 2014-07-24 |   2016-07-01 | July, 01 2016 00:00:00+0000 |
| 2014-07-30 |   2016-07-01 | July, 01 2016 00:00:00+0000 |
| 2014-08-05 |   2016-07-01 | July, 01 2016 00:00:00+0000 |
| 2014-08-11 |   2016-07-01 | July, 01 2016 00:00:00+0000 |
| 2014-08-17 |   2016-07-01 | July, 01 2016 00:00:00+0000 |
| 2015-06-24 |   2016-07-01 | July, 01 2016 00:00:00+0000 |
| 2015-06-30 |   2016-07-01 | July, 01 2016 00:00:00+0000 |
| 2015-07-06 |   2017-07-01 | July, 01 2017 00:00:00+0000 |
| 2015-07-12 |   2017-07-01 | July, 01 2017 00:00:00+0000 |
| 2015-07-18 |   2017-07-01 | July, 01 2017 00:00:00+0000 |
| 2015-07-24 |   2017-07-01 | July, 01 2017 00:00:00+0000 |
| 2015-07-30 |   2017-07-01 | July, 01 2017 00:00:00+0000 |
| 2015-08-05 |   2017-07-01 | July, 01 2017 00:00:00+0000 |
| 2015-08-11 |   2017-07-01 | July, 01 2017 00:00:00+0000 |

http://sqlfiddle.com/#!6/2d5f4/4

by the way. YYYYMMDD is the safest date format of all in sql server. MM/DD/YYYY is not safe, so while I would like to see you avoid string manipulation when doing date calculations, if you are doing it that way use YYYYMMDD

SQL that outputs the last July 1 that is less than two years away

3 Answers3