CODEWITHSUNDEEP
sqlsql-server
sqlearner
sqlearner

Reputation: 65

Adding Current Values to future Dates SQL

I'm trying to load data for future dates. My scenario is something like this, I have data for 2016, 2017 and 2018(until August). Each year has Current and 'Prior Year Same Day' values associated. Data is aggregated to Month. Requirement is that I load data for future dates as well, that is for September 2018 through August 2019 using 'PYSD' values. For example, the 'Current' value for September 2017 will be 'PYSD' value for September 2018 and so on. When I try this particular logic I get an error stating 'overflow' and I'm unable to figure out what's causing. This happens only with the second condition in my WHERE clause. My SELECT clause aggregates a months' data and outputs as '08-01-2018' - there will be one row per month/year.

SELECT 
    CAST(YEAR(DATEADD(dd, 364, CalendarDate)) AS VARCHAR(250)) 
     + '-' + RIGHT('00' + CAST(MONTH(DATEADD(dd, 364, CalendarDate)) AS VARCHAR(250)), 2) 
     + '-' + '01' AS MonthPeriod 
FROM
    DateTable 
WHERE  
    DATEADD(DD, 364, CalendarDate) > '08-31-2018' 
    AND DATEADD(DD, 364, CalendarDate) < '08-01-2019'

My error:

Msg 517, Level 16, State 3, Line 1
Adding a value to a 'date' column caused an overflow.

Also, open to suggestions if there is a simpler way. I'm on SQL Server.

TIA.

Added sample data:

enter image description here

Upvotes: 0

Views: 2218

Answers (2)

Squirrel
Squirrel

Reputation: 24803

What is the maximum date in your DateTable ? You probably have dates that is with year 9999. So adding 364 will cause it to overflow. It has nothing to do with the SELECT clause

On your query, you can simply do this

SELECT DATEADD(MONTH, 
               DATEDIFF(MONTH, 0, 
                        DATEADD(DAY, 364, CalendarDate)), 0) AS MonthPeriod

Note : DATEADD(MONTH, DATEDIFF(MONTH, 0, <date>), 0) will gives you the 1st day of the month

EDIT :

The overflow error is because the DateTable contains rows with date 9999. Adding 364 days to it will cause over flow.

One quick way to resolve is to delete those rows where year is 9998 or 9999

Another way is to change your query such that it does not add days to the CalendarDate

WHERE  CalendarDate > DATEADD(DAY, 364, '2018-08-31')
AND    CalendarDate < DATEADD(DAY, 364, '2019-08-01')

Note : i uses date string in ISO format YYYY-MM-DD. It is recommended to specify date in such format as it is unambiguous.

And the complete query will be

SELECT DATEADD(MONTH, 
               DATEDIFF(MONTH, 0, 
                        DATEADD(DAY, 364, CalendarDate)), 0) AS MonthPeriod 
FROM   DateTable
WHERE  CalendarDate > DATEADD(DAY, 364, '2018-08-31')
AND    CalendarDate < DATEADD(DAY, 364, '2019-08-01')

Upvotes: 1

TomC
TomC

Reputation: 2814

As you stated in one of the answers to the original question, your max date is 9999-12-31

This means that any sort of DATEADD(DD, 364, CalendarDate) should always fail. Doesn't matter if you are trying to check against a date in 2018, because it has to do the dateadd first, and that gets an overflow.

To avoid that overflow do your date add the other way - compare the actual CalendarDate to one year before your target date.

CalendarDate > DATEADD(DD, -364, '08-31-2018')

or just 

CalendarDate > '2017-08-31'

I'll leave the rest of how you should specify the date, or whether it should be 364,365,first of month etc to the rest of the discussion.

Upvotes: 2

Related Questions