Displaying Objects within a Date Range
-
Here is my problem: I have a job posting board that displays job postings for a certain about of time. Each posting in the database has a start date and an end date. Both are datetime fields that contain the time of 00:00:00.0000. Now, when i go to display these jobs on the site I use the following query to get them:
SELECT TOP (10) Jobs.jobID, Job\_Display.displayID, Jobs.title, Entity.entityName, Jobs.locationFROM Entity INNER JOIN
Jobs ON Entity.entID = Jobs.entID INNER JOIN
Job_Display ON Jobs.jobID = Job_Display.jobID
WHERE datediff(dd, getdate(), job_display.endDate) > -1 AND (Job_Display.display = 1)
order by job_display.displayID descHere is there the problem is: Say a job ended today (11/30/2011) it won't show up on the site, but what is intended is that it will be on the site until 12/1/2011. What do I need to change in the where clause to fix this issue?
-
Here is my problem: I have a job posting board that displays job postings for a certain about of time. Each posting in the database has a start date and an end date. Both are datetime fields that contain the time of 00:00:00.0000. Now, when i go to display these jobs on the site I use the following query to get them:
SELECT TOP (10) Jobs.jobID, Job\_Display.displayID, Jobs.title, Entity.entityName, Jobs.locationFROM Entity INNER JOIN
Jobs ON Entity.entID = Jobs.entID INNER JOIN
Job_Display ON Jobs.jobID = Job_Display.jobID
WHERE datediff(dd, getdate(), job_display.endDate) > -1 AND (Job_Display.display = 1)
order by job_display.displayID descHere is there the problem is: Say a job ended today (11/30/2011) it won't show up on the site, but what is intended is that it will be on the site until 12/1/2011. What do I need to change in the where clause to fix this issue?
Just a suggestion. The call to getDate is also going to include the current time. Have the time component truncated or set to '00:00:00.0000' as well. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
Here is my problem: I have a job posting board that displays job postings for a certain about of time. Each posting in the database has a start date and an end date. Both are datetime fields that contain the time of 00:00:00.0000. Now, when i go to display these jobs on the site I use the following query to get them:
SELECT TOP (10) Jobs.jobID, Job\_Display.displayID, Jobs.title, Entity.entityName, Jobs.locationFROM Entity INNER JOIN
Jobs ON Entity.entID = Jobs.entID INNER JOIN
Job_Display ON Jobs.jobID = Job_Display.jobID
WHERE datediff(dd, getdate(), job_display.endDate) > -1 AND (Job_Display.display = 1)
order by job_display.displayID descHere is there the problem is: Say a job ended today (11/30/2011) it won't show up on the site, but what is intended is that it will be on the site until 12/1/2011. What do I need to change in the where clause to fix this issue?
-
Here is my problem: I have a job posting board that displays job postings for a certain about of time. Each posting in the database has a start date and an end date. Both are datetime fields that contain the time of 00:00:00.0000. Now, when i go to display these jobs on the site I use the following query to get them:
SELECT TOP (10) Jobs.jobID, Job\_Display.displayID, Jobs.title, Entity.entityName, Jobs.locationFROM Entity INNER JOIN
Jobs ON Entity.entID = Jobs.entID INNER JOIN
Job_Display ON Jobs.jobID = Job_Display.jobID
WHERE datediff(dd, getdate(), job_display.endDate) > -1 AND (Job_Display.display = 1)
order by job_display.displayID descHere is there the problem is: Say a job ended today (11/30/2011) it won't show up on the site, but what is intended is that it will be on the site until 12/1/2011. What do I need to change in the where clause to fix this issue?
How about
WHERE GETDATE() BETWEEN startDate AND endDate? BETWEEN is inclusive. Also, (if SQL Server) maybe look nito the DATE datatype. -
Here is my problem: I have a job posting board that displays job postings for a certain about of time. Each posting in the database has a start date and an end date. Both are datetime fields that contain the time of 00:00:00.0000. Now, when i go to display these jobs on the site I use the following query to get them:
SELECT TOP (10) Jobs.jobID, Job\_Display.displayID, Jobs.title, Entity.entityName, Jobs.locationFROM Entity INNER JOIN
Jobs ON Entity.entID = Jobs.entID INNER JOIN
Job_Display ON Jobs.jobID = Job_Display.jobID
WHERE datediff(dd, getdate(), job_display.endDate) > -1 AND (Job_Display.display = 1)
order by job_display.displayID descHere is there the problem is: Say a job ended today (11/30/2011) it won't show up on the site, but what is intended is that it will be on the site until 12/1/2011. What do I need to change in the where clause to fix this issue?
Convert GetDate() to a date to get rid of the time component (assumes SQL Server 2008).
SELECT CONVERT(DATE,GETDATE())
Never underestimate the power of human stupidity RAH