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

x
?
Solved

select date sql (only show results within this week)

Posted on 2004-09-28
12
Medium Priority
?
537 Views
Last Modified: 2006-11-17
I am trying to select all the records to display that are only within the current week

This is my SQL

SELECT date, projectnumber, fullname, hoursworked , code, description
            FROM userData
            INNER JOIN projects
            ON userData.projectnumber = projects.number
            where fullname ='Bob Smith'
            AND  DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= date
            ORDER BY date ASC

the  DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= date
part does not return the correct data
results:
2004-09-22
2004-09-24
2004-09-27
2004-10-01
2004-10-30

I only want to show the records between
2004-09-26 AND 2004-10-02
(the current work week)

Then on Sunday of next week I only want to show the records for that week
2004-10-03  to 2004-10-09
and so on each week there after.

What do I need to change to get only the records to show for the current week?

Thanks
0
Comment
Question by:Timothy Golden
  • 6
  • 5
12 Comments
 
LVL 3

Author Comment

by:Timothy Golden
ID: 12171453
anyone have a sugestion?

i guess i need to bump the points????

0
 
LVL 9

Expert Comment

by:gtkfreak
ID: 12172370
In where clause, put in:
WEEK(day) = Week(curdate())

Trust it helps you.

0
 
LVL 3

Author Comment

by:Timothy Golden
ID: 12172393
is this instead of this:
DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= date

so my SQL would be this:

SELECT id,date, projectnumber, fullname,hoursworked ,code, description
            FROM userData
            INNER JOIN projects
            ON userData.projectnumber = projects.number
            where fullname ='Bob Smith'
            AND WEEK(day) = Week(curdate())
            ORDER BY date ASC
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 3

Author Comment

by:Timothy Golden
ID: 12172412
changing to this did not hlep:
SELECT id,date, projectnumber, fullname,hoursworked ,code, description
          FROM userData
          INNER JOIN projects
          ON userData.projectnumber = projects.number
          where fullname ='Bob Smith'
          AND WEEK(day) = Week(curdate())
          ORDER BY date ASC

any other suggestions?
0
 
LVL 13

Expert Comment

by:cLFlaVA
ID: 12172947
Wouldn't you want DATE_ADD instead of DATE_SUB?
0
 
LVL 3

Author Comment

by:Timothy Golden
ID: 12172976
not sure that's why im asking :)

0
 
LVL 13

Expert Comment

by:cLFlaVA
ID: 12172989
Like this?


    SELECT date
              , projectnumber
              , fullname
              , hoursworked
              , code
              , description
      FROM userData u INNER JOIN projects p
          ON u.projectnumber = p.number
    WHERE fullname = 'Bob Smith'
         AND date >= CURDATE()
         AND date < DATEADD(CURDATE(), INTERVAL 7 DAY)
ORDER BY date ASC
0
 
LVL 13

Expert Comment

by:cLFlaVA
ID: 12172993
and that should be DATE_ADD not DATEADD
0
 
LVL 13

Expert Comment

by:cLFlaVA
ID: 12173034
Also, it's unclear which columns you're requesting from which tables...maybe you should make it a little more explicit, for readability sake:

    SELECT u.date
              , u.projectnumber
              , u.fullname
              , u.hoursworked
              , u.code
              , u.description
      FROM userData u INNER JOIN projects p
          ON u.projectnumber = p.number
    WHERE u.fullname = 'Bob Smith'
         AND date >= CURDATE()
         AND date < DATEADD(CURDATE(), INTERVAL 7 DAY)
ORDER BY date ASC

Of course, I don't know which tables these fields are coming from, so I put the u...
0
 
LVL 3

Author Comment

by:Timothy Golden
ID: 12173172
does this help?

SELECT userData.id,
      userData.date,
      userData.projectnumber,
      userData.fullname,
      userData.hoursworked,
      projects.code,
      projects.description
      
      FROM userData
      
      INNER JOIN projects
      
      ON userData.projectnumber = projects.number
      
      where fullname ='Bob Smith'
      
      AND  DATE_ADD(CURDATE(),INTERVAL 7 DAY) <= userData.date
      
      ORDER BY date ASC

0
 
LVL 13

Accepted Solution

by:
cLFlaVA earned 100 total points
ID: 12173212
Does it work???

    SELECT u.date
              , u.projectnumber
              , u.fullname
              , u.hoursworked
              , p.code
              , p.description
      FROM userData u INNER JOIN projects p
          ON u.projectnumber = p.number
    WHERE u.fullname = 'Bob Smith'
         AND u.date >= CURDATE()
         AND u.date < DATE_ADD(CURDATE(), INTERVAL 7 DAY)
ORDER BY u.date ASC
0
 
LVL 3

Author Comment

by:Timothy Golden
ID: 12173450
thanks!  that was it!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month9 days, 17 hours left to enroll

926 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