Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 504
  • Last Modified:

Calculating 7 days ahead of currentdate?

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

0
Westside2004
Asked:
Westside2004
  • 3
  • 2
1 Solution
 
AaronAbendCommented:
where mydatecolumn between getdate() and getdate()+7

0
 
Scott PletcherSenior DBACommented:
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.
0
 
AaronAbendCommented:
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)
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

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

Yes, that worked.  Thanks for the quick responses.

-ws
0
 
Scott PletcherSenior DBACommented:
>> 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.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now