System Date

SELECT team_name FROM team WHERE date = sysdate -6

I know this code is wrong but it is the best way of explaining what I'm trying
to do. Is there anyone that knows the correct query?

I'm trying to pull a team name that has a date stored with it but i want it to
work for any of the days inside a week.I hope this makes sense!

Assuming that team_name and date are fields in a table named 'team', you may
want to try

 SELECT team_name FROM team WHERE date = GetDate();

you might want to do this ( since getdate returns upto milli second ) . 

SELECT team_name FROM team WHERE datediff(day,date GetDate() ) = 0 ;

If you find a MySql syntax a good one is :

SELECT team_name FROM team WHERE date = CURDATE();

 If you 're looking for more details : go to mysql.com
(http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html)

I hope it help you.