Westside2004
asked on
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(dateti me,CONVERT (varchar(1 5),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
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(dateti
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
where mydatecolumn between getdate() and getdate()+7
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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(date time, 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)
where mydatecolumn>=convert(date
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.
ASKER
HI,
Yes, that worked. Thanks for the quick responses.
-ws
Yes, that worked. Thanks for the quick responses.
-ws
>> 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.
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.