Solved

Calculating 7 days ahead of currentdate?

Posted on 2006-10-30
6
489 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

0
Comment
Question by:Westside2004
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 10

Expert Comment

by:AaronAbend
ID: 17836530
where mydatecolumn between getdate() and getdate()+7

0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 125 total points
ID: 17836546
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
 
LVL 10

Expert Comment

by:AaronAbend
ID: 17836589
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 10

Expert Comment

by:AaronAbend
ID: 17836600
sorry - to clarify - in my second comment, "this" was referring to my original post, not Scott's comment.
0
 
LVL 1

Author Comment

by:Westside2004
ID: 17836982
HI,

Yes, that worked.  Thanks for the quick responses.

-ws
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 17837079
>> 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question