• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

Display an entire week from one day

I asked a similar question but thought I would ask it in a different way.

If I have one day, November 29th 2013 and I want to choose every thing in DB table that has that date, but I want to SELECT everything in the database for the week of Nov 29th 2013 how do I do that.  The date will always change so I don't have a start and finish date so I have to use php to determine the entire week containing November 29th
0
rgranlund
Asked:
rgranlund
  • 6
  • 5
  • 2
1 Solution
 
Melih SARICAOwnerCommented:
select * from TableName where datepart(ISOWEEK,dateColumn) = datepart(ISOWEEK,@requestDate)
0
 
rgranlundAuthor Commented:
@non_zero can you write that using November 29 2013.  I'm still learning php and am a newbie.
0
 
Melih SARICAOwnerCommented:
declare @requestDate date
set @requestDate= '2013-10-29'

select * from TableName where datepart(ISOWEEK,dateColumn) = datepart(ISOWEEK,@requestDate)
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
rgranlundAuthor Commented:
@non_zero, almost there... but what am I doing wrong here?

	$requestDate = '2013-10-29';


	$bg = '#eeeeee'; 
		
	$sql->query("SELECT doctor_doctor.doctor_id, doctor_doctor.first_name AS first_name, doctor_doctor.last_name AS last_name, doctor_schedule.doctor_id, doctor_schedule.schedule_date AS date, doctor_schedule.time AS time, doctor_schedule.location_id
	FROM doctor_doctor, doctor_schedule
	WHERE where datepart(ISOWEEK, doctor_schedule.schedule_date) = datepart(ISOWEEK, $requestDate) AND doctor_schedule.schedule_date='$new_date' AND doctor_doctor.doctor='1' AND doctor_doctor.doctor_id=doctor_schedule.doctor_id AND doctor_schedule.location_id='1' 
	ORDER BY date DESC");

Open in new window

0
 
Ray PaseurCommented:
Try something like this...

$alpha = date('c', strtotime('Nov 29, 2013'));
$omega = date('c', strtotime('Nov 29, 2013 + 1 week'));
$query = "SELECT... WHERE important_date BETWEEN '$alpha' AND '$omega'";

Open in new window

0
 
Melih SARICAOwnerCommented:
What is the result u get from that query..

I guess $newdate must be requestdate

and also between datepart and newdate it must be or not and to get the entire week

$new_date = '2013-10-29';


      $bg = '#eeeeee';
            
      $sql->query("SELECT doctor_doctor.doctor_id, doctor_doctor.first_name AS first_name, doctor_doctor.last_name AS last_name, doctor_schedule.doctor_id, doctor_schedule.schedule_date AS date, doctor_schedule.time AS time, doctor_schedule.location_id
      FROM doctor_doctor, doctor_schedule
      WHERE (datepart(ISOWEEK, doctor_schedule.schedule_date) = datepart(ISOWEEK, $new_date) or doctor_schedule.schedule_date='$new_date' ) AND doctor_doctor.doctor='1' AND doctor_doctor.doctor_id=doctor_schedule.doctor_id AND doctor_schedule.location_id='1'
      ORDER BY date DESC");
0
 
rgranlundAuthor Commented:
There was a typo in my Query.  However, I get the ERROR:
Message - FUNCTION thedocto_db.datepart does not exist

Can you shed any light on that error?
0
 
Melih SARICAOwnerCommented:
IS ur Server MSSQL Server or Something else.. Code i send is for MSSQL Server
0
 
rgranlundAuthor Commented:
@RAy, the problem is, it does not take into consideration the entire week.  Only one day that is not the start of the week
0
 
rgranlundAuthor Commented:
@non_zero MySQL

	$sql->query("SELECT doctor_doctor.doctor_id, doctor_doctor.first_name AS first_name, doctor_doctor.last_name AS last_name, doctor_schedule.doctor_id, doctor_schedule.schedule_date AS date, doctor_schedule.time AS time, doctor_schedule.location_id
	FROM doctor_doctor, doctor_schedule
	WHERE datepart(ISOWEEK, doctor_schedule.schedule_date) = datepart(ISOWEEK, '$requestDate') AND doctor_doctor.doctor='1' AND doctor_doctor.doctor_id=doctor_schedule.doctor_id AND doctor_schedule.location_id='1' 
	ORDER BY doctor_schedule.schedule_date DESC");

Open in new window

0
 
Melih SARICAOwnerCommented:
$sql->query("SELECT doctor_doctor.doctor_id, doctor_doctor.first_name AS first_name, doctor_doctor.last_name AS last_name, doctor_schedule.doctor_id, doctor_schedule.schedule_date AS date, doctor_schedule.time AS time, doctor_schedule.location_id
      FROM doctor_doctor, doctor_schedule
      WHERE YEARWEEK(doctor_schedule.schedule_date) =  YEARWEEK('$requestDate') AND doctor_doctor.doctor='1' AND doctor_doctor.doctor_id=doctor_schedule.doctor_id AND doctor_schedule.location_id='1'
      ORDER BY doctor_schedule.schedule_date DESC");
0
 
Melih SARICAOwnerCommented:
USE YEARWEEK INSTEAD OF DATEPART
0
 
Ray PaseurCommented:
This part kind of threw me off...
If I have one day, November 29th 2013 and I want to choose every thing in DB table that has that date, but I want to SELECT everything in the database for the week of Nov 29th 2013 how do I do that.
Why would you want to do that?  Normally, you would either need a results set with a day or a results set with a week.  SELECT is a term of art in software development.  Choose is not.

These sorts of questions are best framed by creating the SSCCE.  For this, you would post the CREATE TABLE statement, along with the information needed to load the test data into the table.  Then you would show us by example, exactly which rows you want to get from the query.  The advantage of doing it this way, instead of trying to describe what you want, is that you will (1) remove any ambiguity in the language and (2) often solve the problem yourself, without having to guess.  It's a great tool/concept and it really makes asking questions fast and easy.
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.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now