We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

# Calculating 7 days ahead of currentdate?

on
Medium Priority
546 Views
Hi,

I am trying to create a query that will give me all the records that have a date equal to currentDate + 7, so all records that have a date between the current date and 1 week basically.

My sql seems to be a bit off here

SELECT m.*, c.fname + ' ' + c.lname as fullname, cat.category as categoryName
FROM table m
INNER JOIN categories cat ON cat.categoryId = m.categoryId
LEFT JOIN customer c ON c.customerId = m.customerId

Any help appreciated.  I am looking for all records that have a value in the 'myDateColumn' that is between the currentDate and 1 week.  I am not interested in time at this point as we are only storing the date in the db.

-ws

Comment
Watch Question

## View Solution Only

Commented:
where mydatecolumn between getdate() and getdate()+7

Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
WHERE myDateColumn BETWEEN CONVERT(CHAR(8), GETDATE(), 112) AND DATEADD(DAY, 7, CONVERT(CHAR(8), GETDATE(), 112))

You may need to change 7 to 6, depending on specifically how you want it to work.

Not the solution you were looking for? Getting a personalized solution is easy.

Commented:
note ... this will not get a record that happened "today" but 5 minutes ago... if you want everything from midnight now to 11:59:59 pm of the day a week from today, you truncate the date ...

where mydatecolumn>=convert(datetime, convert(varchar,getdate(),1)) and mydatecolumn < convert(datetime, convert(varchar,getdate()+8,1))

This approach (going ahead an extra day but saying less than instead of less-than-or-equal) has the advantage of not manipulating your datecolumn, which makes it faster (manipulating a column can keep the optimizer from using an index on it)

Commented:
sorry - to clarify - in my second comment, "this" was referring to my original post, not Scott's comment.

Commented:
HI,

Yes, that worked.  Thanks for the quick responses.

-ws
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
>> I am not interested in time at this point as we are only storing the date in the db. <<

Btw, if the column type is "datetime" or "smalldatetime", SQL *is* storing the time -- if will default to midnight unless otherwise specified.  That's why I stripped the time, using the CONVERTs, in my code.
##### Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile