CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Litation
Litation

Reputation: 37

SQL DATEADD with Variable

Just wondering if I'm missing something really simple with the DATEADD and a variable and I'm sure there must be a more elegant way to do what I want.

I currently have a load of spiel but as part of that I have

DECLARE @EndDate date

SET @EndDate = '2016-02-01'
SET @EndDate = DATEADD(DD,1, @EndDate)

Now in the final version the first @EndDate is set with a select statement however for testing I have to hardcode it in as it's generated as the user starts the report and rather than generate the report every single time it makes it simple. The idea is that when the user inputs a date the search is inclusive of that date rather than just going up to midnight the day before.

If it helps here is the select statement that will be used.

     DECLARE @EndDate date

    SET @EndDate = (SELECT atvcEndDate FROM tbUserReport WHERE atvcUserId = @UserId)
    SET @EndDate = DATEADD(DD,1, @EndDate)

Is there anyway to compress it down to one line, its not massive in the grand scheme of things but as this is something that will be in every storedproc we build for this it seems like something to save a bit of time (as well as expand my knowledge which is the real reason for me posting this!)

Cheers in advance.

Upvotes: 1

Views: 9688

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Yes. Do the DATEADD() inside the SELECT:

 DECLARE @EndDate date;

 SELECT @EndDate = DATEADD(day, 1, atvcEndDate)
 FROM tbUserReport
 WHERE atvcUserId = @UserId;

Notes:

  • Use semicolons to terminate each statement (makes it easier to follow the logic).
  • The nested SELECT in the SET is not necessary. A SELECT can assign a variable.
  • Both of these assume that there is (at most) one match for a given @UserId (probably reasonable).
  • And, for the date part functions, I strongly advise spelling out the date part ("year", "month", "day") instead of abbreviations. I think this makes the code more understandable and maintainable.

Upvotes: 5

Related Questions