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?

Westside2004
Westside2004 asked
on
Medium Priority
546 Views
Last Modified: 2008-02-26
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
WHERE DATEADD(d,7,CONVERT(datetime,CONVERT(varchar(15),GETDATE(),101))) >= myDateColumn

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

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.

Ask the Experts
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)
sorry - to clarify - in my second comment, "this" was referring to my original post, not Scott's comment.

Author

Commented:
HI,

Yes, that worked.  Thanks for the quick responses.

-ws
Scott PletcherSenior 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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

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

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.