SQL Server - Date arithmetic - adding days

Asked By DukeCare on 12-Jul-07 08:22 AM
If you are adding days to a date is there a compelling reason to use
DATEADD()rather than

datevalue + #days

Aaron Bertrand [SQL Server MVP] replied on 12-Jul-07 08:28 AM
Clarity, mostly.  Think about other people who have to read the code... is
the next person going to know that date addition/subtraction is in days?
DukeCare replied on 12-Jul-07 08:48 AM
Aaron -

I'm not that experienced in T-SQL, and have made the assumption that any
time you add an integer to a datevalue you are, by definition, adding days.
Is that a bad assumption?

At any rate, I accept your reason as valid.  Thanks
Aaron Bertrand [SQL Server MVP] replied on 12-Jul-07 08:54 AM
It's not a bad assumption, mainly because it's correct.  However, not
everyone is going to come to that same assumption, and if that person
assumes wrong and then changes the code to use DATEADD(MINUTE or MONTH...

Aaron Bertrand [SQL Server MVP] replied on 12-Jul-07 08:55 AM
Another reason to use DATEADD() is if you are going to change the logic from
(dt)-1 to (dt) - 12 hours or (dt) - 1 week, the code is easier to maintain.
That's a weak reason.  As an aside, I use simple integer math for days, but
that doesn't mean I consider it best practice.  :-)
Tibor Karaszi replied on 12-Jul-07 12:36 PM
I agree. Whenever I see just an addition, I have to stop and think for a while. If I see DATEADD, I
know immediately what it does.

Tibor Karaszi, SQL Server MVP
ctoto replied on 12-Jul-07 02:12 PM
On Jul 12, 5:22 am, Duke Carey <DukeCa...@>

Not much of a difference when you only add 1 to 7 days, what about
adding 31 days? Is this a month? Is this in February, then should it
be 28 days/29 days/30 days... DATEADD will take care of the various
units, week, month, quater, year...

See: http://www.sqlhacks.com/index.php/Dates/Dates

Also new this week:

How to significantly improve the speed of count(*)
What's the difference between sp_who and sp_who2
Microsoft provides a free decent performance monitor system
How Microsoft SQL Server deals with aggregates including averages and
How to a list of all the missing numbers in an identity column
How many ways can one rewrite a very simple query
How to get how many records are in all the tables
Personal preferences for stored procedures naming conventions
How to select sales during banking hours
Why am I getting wrong results with datediff
What the difference between timestamp and current_timestamp
What has the best performance operator, the IN query or the OR query
FAQs about Common Tables Expressions CTE
How MSSQL deals with timestamps
What's the difference between varchar and nvarchar
Simple query for the various setting of a database
How to convert string to dates and times and back
How to see and document all the views
How to access the previous row or the next row using set operations
and without using any cursor