August 3, 2009 at 7:58 am
I feel really silly, but I'm having a lot of trouble with this and could use some help. I have a table of year/term/session information with start_date and end_date for each year/term/session. I want to pull records for sessions two weeks prior to their start date and I want to continue pulling them until two weeks after their end date. I'm struggling with the syntax to compare them to today's date.
I was doing this:
where getdate()+15 between a.start_Date and a.end_date
That worked great for the start date, but it stopped pulling the records two weeks before the end date and I need it two weeks after.
August 3, 2009 at 8:36 am
It would help if you would please provide the DDL for the table, sample data in a readily consummable format, expected results based on the sample data.
For help providing this, please read the first article in my signature block. It provides excellent instructions on how to post to get the best, tested answers quickly.
August 3, 2009 at 9:02 am
Thanks, Lynn. I will try to follow the article's recommendations.
--create table
CREATE TABLE [dbo].[ACADEMICCALENDAR](
[ACADEMIC_YEAR] [varchar](4) NOT NULL,
[ACADEMIC_TERM] [varchar](10) NOT NULL,
[ACADEMIC_SESSION] [varchar](10) NOT NULL,
[START_DATE] [datetime] NULL,
[END_DATE] [datetime] NULL
CONSTRAINT [ACACALENDAR_PK] PRIMARY KEY CLUSTERED
(
[ACADEMIC_YEAR] ASC,
[ACADEMIC_TERM] ASC,
[ACADEMIC_SESSION] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
--populate table with data
insert into academiccalendar
(ACADEMIC_YEAR,ACADEMIC_TERM,ACADEMIC_SESSION,START_DATE,END_DATE)
SELECT '2009','FALL','DOCT ED','Sep 5 2009 12:00AM','Dec 5 2009 12:00AM' UNION ALL
SELECT '2009','FALL','DOCT PSYCH','Aug 25 2009 12:00AM','Dec 10 2009 12:00AM' UNION ALL
SELECT '2009','FALL','GRAD ED','Aug 15 2009 12:00AM','Dec 12 2009 12:00AM' UNION ALL
SELECT '2009','FALL','GRAD LIB','Aug 22 2009 12:00AM','Dec 5 2009 12:00AM' UNION ALL
SELECT '2009','FALL','GRAD PA','Sep 1 2009 12:00AM','Dec 17 2009 12:00AM' UNION ALL
SELECT '2009','FALL','GRAD PSYCH','Aug 22 2009 12:00AM','Dec 5 2009 12:00AM' UNION ALL
SELECT '2009','FALL','GRAD REL','Sep 14 2009 12:00AM','Nov 20 2009 12:00AM' UNION ALL
SELECT '2009','FALL','PRE MHR','Aug 15 2009 12:00AM','Dec 15 2009 12:00AM' UNION ALL
SELECT '2009','FALL','TRAD','Sep 1 2009 12:00AM','Dec 17 2009 12:00AM' UNION ALL
SELECT '2009','SUMMER','MHR 198','Jun 4 2009 12:00AM','Oct 15 2009 12:00AM' UNION ALL
SELECT '2009','SUMMER','MHR 199','Jun 30 2009 12:00AM','Nov 3 2009 12:00AM' UNION ALL
SELECT '2009','SUMMER','MHR 200','Jul 7 2009 12:00AM','Oct 13 2009 12:00AM' UNION ALL
SELECT '2009','SUMMER','MHR 201','Jul 30 2009 12:00AM','Dec 10 2009 12:00AM' UNION ALL
SELECT '2009','SUMMER','MHR 204','Jun 22 2009 12:00AM','Oct 5 2009 12:00AM' UNION ALL
SELECT '2009','SUMMER','MHR 205','Jul 14 2009 12:00AM','Oct 20 2009 12:00AM' UNION ALL
SELECT '2009','SUMMER','MHR 206','Aug 4 2009 12:00AM','Nov 10 2009 12:00AM' UNION ALL
SELECT '2009','SUMMER','MHR 211','Aug 11 2009 12:00AM','Dec 15 2009 12:00AM' UNION ALL
SELECT '2009','SUMMER','MSM 33','Jul 9 2009 12:00AM','Dec 17 2009 12:00AM' UNION ALL
SELECT '2009','SUMMER','PRE MHR','Jun 2 2009 12:00AM','Aug 15 2009 12:00AM'
--expected results
'2009','FALL','GRAD ED','Aug 15 2009 12:00AM','Dec 12 2009 12:00AM'
'2009','FALL','PRE MHR','Aug 15 2009 12:00AM','Dec 15 2009 12:00AM'
'2009','SUMMER','MHR 198','Jun 4 2009 12:00AM','Oct 15 2009 12:00AM'
'2009','SUMMER','MHR 199','Jun 30 2009 12:00AM','Nov 3 2009 12:00AM'
'2009','SUMMER','MHR 200','Jul 7 2009 12:00AM','Oct 13 2009 12:00AM'
'2009','SUMMER','MHR 201','Jul 30 2009 12:00AM','Dec 10 2009 12:00AM'
'2009','SUMMER','MHR 204','Jun 22 2009 12:00AM','Oct 5 2009 12:00AM'
'2009','SUMMER','MHR 205','Jul 14 2009 12:00AM','Oct 20 2009 12:00AM'
'2009','SUMMER','MHR 206','Aug 4 2009 12:00AM','Nov 10 2009 12:00AM'
'2009','SUMMER','MHR 211','Aug 11 2009 12:00AM','Dec 15 2009 12:00AM'
'2009','SUMMER','MSM 33','Jul 9 2009 12:00AM','Dec 17 2009 12:00AM'
'2009','SUMMER','PRE MHR','Jun 2 2009 12:00AM','Aug 15 2009 12:00AM'
--what I've tried
where a.start_date getdate()
/*This half worked. It brought back the courses that are starting, but it didn't pull back any of the ones whose start_date was outside the range but whose end date hadn't passed.*/
August 3, 2009 at 9:11 am
lduvall (8/3/2009)
I want to pull records for sessions two weeks prior to their start date and I want to continue pulling them until two weeks after their end date. I'm struggling with the syntax to compare them to today's date.I was doing this:
where getdate()+15 between a.start_Date and a.end_date
You want the start date shifted in one direction and the end date shifted in the other direction, so you cannot do that with just one operation. Also, while it's possible to use standard arithmetic operations on dates, I would recommend using the specific DateAdd() or DateDiff() functions to make it clearer that you are working with dates.
WHERE DateDiff(dd, GetDate(), a.Start_Date) <= 14
AND DateDiff(dd,a.end_date, GetDate()) <= 14
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 3, 2009 at 9:29 am
If you define the WHERE clause like this:
WHERE (a.START_DATE >= DATEADD(day, -14, GETDATE()))
AND (a.END_DATE <= DATEADD(day, 14, GETDATE()))
you should get performance improvement as the DATEADD expressions will need to be calculated only once and the query can make use of an index on the START_DATE and END_DATE columns (if such an index exists).
August 3, 2009 at 9:42 am
Thank you both so much!
August 3, 2009 at 10:12 am
Not sure if the above actually works. I got an empty result set with this WHERE clause
WHERE
a.START_DATE >= dateadd(dd,-14,getdate()) and
a.END_DATE <= dateadd(dd,14,getdate())
The attached code returns a result set that matches the expected results set provided by the OP.
August 3, 2009 at 10:46 am
Sorry! - that'll teach me to submit an answer without testing it.
However, a minor change fixes the problem I think.
WHERE (a.START_DATE = DATEADD(day, -14, GETDATE()))
Here's a variation that removes the time part from the datetime value returned by GETDATE(). You may prefer this alternative in order to get consistent results when the query is run at different times throughout a day.
WHERE (a.START_DATE = DATEADD(day, DATEDIFF(day, 0, GETDATE()), -14))
August 3, 2009 at 10:56 am
andrewd.smith (8/3/2009)
Sorry! - that'll teach me to submit an answer without testing it.However, a minor change fixes the problem I think.
WHERE (a.START_DATE = DATEADD(day, -14, GETDATE()))Here's a variation that removes the time part from the datetime value returned by GETDATE(). You may prefer this alternative in order to get consistent results when the query is run at different times throughout a day.
WHERE (a.START_DATE = DATEADD(day, DATEDIFF(day, 0, GETDATE()), -14))
Yes, my tests confirm this one. Just had some difficulty initially coming up with the correct filter criteria to match the BETWEEN criteria. Brain must still be recovering from soccer this weekend.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply