I have a variable @var1 date time
Will the below query work Set @var1 = @var1 +1
I want to increment date by 1 in @var1
Will the above method or Dateadd(dd,1,@var1) be more efficent
1 Answer
(Even if it syntactically valid, it is not immediately obvious to the next person who reads your code what is actually happening).
In some platforms, date-and-time values are radixed on the day, so x + 1 is the next calendar day, and a fractional day is that number of seconds or minutes.
Other platforms store or represent date-and-time values as integer seconds (or miliseconds, or even nanoseconds) since some epoch value, in which case x + 1 adds 1 second or whatever the base unit is to the value.
For best results (consistent on every platform, and to be immediately obvious) use the platform's built-in date library. In SQL that is the DATEADD function, where you specify the quantity you want to add to the date.
Your question asks if + 1 or DATEADD is "more efficient". I'll say you don't use SQL to be "efficient" - there are inherent inefficiencies in SQL and database systems; it can be said developer productivity and bug-avoidance is far more important, in which case you should use DATEADD.
DATEADD(), always. Forget about performance, but the lazy+1syntax is not only not intuitive to the next person who reads the code, it also won't work with the new data types (date,datetime2, etc).