select date sql (only show results within this week)

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
LVL 3
Timothy GoldenWeb DevAsked:
Who is Participating?
 
cLFlaVAConnect With a Mentor Commented:
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
 
Timothy GoldenWeb DevAuthor Commented:
anyone have a sugestion?

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

0
 
gtkfreakCommented:
In where clause, put in:
WEEK(day) = Week(curdate())

Trust it helps you.

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Timothy GoldenWeb DevAuthor Commented:
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
 
Timothy GoldenWeb DevAuthor Commented:
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
 
cLFlaVACommented:
Wouldn't you want DATE_ADD instead of DATE_SUB?
0
 
Timothy GoldenWeb DevAuthor Commented:
not sure that's why im asking :)

0
 
cLFlaVACommented:
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
 
cLFlaVACommented:
and that should be DATE_ADD not DATEADD
0
 
cLFlaVACommented:
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
 
Timothy GoldenWeb DevAuthor Commented:
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
 
Timothy GoldenWeb DevAuthor Commented:
thanks!  that was it!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.