SQL Server Performance Forum – Threads Archive

Easy way to find out if date is in a given period

Hi! I got two questions related to my application. Its database structure looks like this: RowID | StartDate | EndDate | Amount
—————————————
1 | 26-Feb-07 | 05-Apr-07 | 1111
2 | 08-Apr-07 | 12-Apr-07 | 2222
3 | 13-Apr-07 | 09-May-07 | 3333
4 | 31-Mar-07 | 11-May-07 | 4444 In the application side the user gives a period (e.g. from 1-Apr-2007 to 30-Apr-2007).
Now I would like to find only those rows, that belongs to given period. How? Obviously it’s relatively simple to find rows 1-3 by using
WHERE (StartDate BETWEEN ’01-Apr-2007′ AND ’30-Apr-2007′) OR (EndDate BETWEEN ’01-Apr-2007′ AND ’30-Apr-2007) .. but how to find the 4th row? The second question concerns calculating the amount of days within the period. Is there an easy way to do this by using SQL functions? The result would be: (only the number is needed)
Row 1: 5 (01-Apr to 05-Apr)
Row 2: 5 (08-Apr to 12-Apr)
Row 3: 18 (13-Apr to 30-Apr)
Row 4: 30 (whole April) Thanks! -Mika

Okay, the answer to the first question was very apparent
… OR (StartDate < ’01-Apr-2007′) AND (EndDate > ’30-Apr-2007′) Any ideas to second question?

Check out the DATEDIFF function. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

Datediff(day,date1,date2) Madhivanan Failing to plan is Planning to fail

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Madhivanan Datediff(day,date1,date2) Madhivanan Failing to plan is Planning to fail <hr height="1" noshade id="quote"></blockquote id="quote"> Nope, there would need to be a CASE expression if Date1 is less than startdate. Likewise for Date2 and EndDate. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

As soon as you start to mix-and-match AND and OR, you need to pay close attention to bracketing. You’re not clear about the given period: If either StartDate or EndDate is within the given period, then you have an overlap:
WHERE ((StartDate BETWEEN @Start AND @End) OR (EndDate BETWEEN @Start AND @End)) If both StartDate and EndDate are within the given period, then the whole period is within the given period:
WHERE (StartDate BETWEEN @Start AND @End) AND (EndDate BETWEEN @Start AND @End) I guess you’re looking for the first option! Note – try to use either the US mm/dd/yyyy date format, or the ‘universal’ yyyy-mm-dd format in your scripts. Spelling out months means you’re depending on the server’s language settings. For the number of days, use the DATEDIFF function: SELECT DATEDIFF(d, StartDate, EndDate) FROM MyTable … and please post this type of question in the "New SQL Server Users / Getting Started" section of the forum,http://www.sql-server-performance.com/forum/forum.asp?FORUM_ID=48

<< Note – try to use either the US mm/dd/yyyy date format, or the ‘universal’ yyyy-mm-dd format in your scripts. >> I would strongly suggest universal format [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

Thanks for prompt replies. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] <blockquote id="quote">quote:<hr height="1" noshade id="quote">If either StartDate or EndDate is within the given period, then you have an overlap: WHERE ((StartDate BETWEEN @Start AND @End) OR (EndDate BETWEEN @Start AND @End)) If both StartDate and EndDate are within the given period, then the whole period is within the given period: WHERE (StartDate BETWEEN @Start AND @End) AND (EndDate BETWEEN @Start AND @End)<hr height="1" noshade id="quote"></blockquote id="quote"> I’m afraid these does not work if neither StartDate or EndDate are in given period (see row 4). E.g. if user defines the period to be from 01-Apr (@Start) to 30-Apr (@End), and StartDate is 31-Mar and EndDate 11-May. In this case StartDate would not be BETWEEN @Start and @End. Same with EndDate.. [email protected]@End ….01-Apr…USER DEFINED PERIOD….. 30-Apr ……|———————————| .31-Mar………………………………11-May …|—————–row4——————–| <blockquote id="quote">quote:<hr height="1" noshade id="quote"> Note – try to use either the US mm/dd/yyyy date format, or the ‘universal’ yyyy-mm-dd format in your scripts. Spelling out months means you’re depending on the server’s language settings.<hr height="1" noshade id="quote"></blockquote id="quote"> This is a good point and I in fact use CONVERT function in the actual code. <blockquote id="quote">quote:<hr height="1" noshade id="quote">… and please post this type of question in the "New SQL Server Users / Getting Started" section of the forum,<a target="_blank" href=http://www.sql-server-performance.com/forum/forum.asp?FORUM_ID=48>http://www.sql-server-performance.com/forum/forum.asp?FORUM_ID=48</a><hr height="1" noshade id="quote"></blockquote id="quote"> Jep.. [:I]

quote:I’m afraid these does not work if neither StartDate or EndDate are in given period (see row 4). E.g. if user defines the period to be from 01-Apr (@Start) to 30-Apr (@End), and StartDate is 31-Mar and EndDate 11-May. In this case StartDate would not be BETWEEN @Start and @End. Same with EndDate..

OK then – to include the oversized period … WHERE ((StartDate BETWEEN @Start AND @End)
OR (EndDate BETWEEN @Start AND @End)
OR (StartDate > @Start AND EndDate < @Start))

quote:I in fact use CONVERT function in the actual code.

Are you applying CONVERT to the table columns? Not recommended! Show us what you have now.

<<
This is a good point and I in fact use CONVERT function in the actual code.
>> Why? Madhivanan Failing to plan is Planning to fail

<blockquote id="quote">quote:<hr height="1" noshade id="quote"> WHERE ((StartDate BETWEEN @Start AND @End) OR (EndDate BETWEEN @Start AND @End) OR (StartDate > @Start AND EndDate < @Start))<hr height="1" noshade id="quote"></blockquote id="quote"> Guess the last OR clause should be like (to find out also row4 in orig. posting): OR (StartDate < @Start) AND (EndDate > @End) Anyway, this seems to work fine now. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] SELECT *, DATEDIFF(D, StartDate, EndDate) +1 AS IntervalSteps, DATEDIFF(D, StartDate, @Start) – 1 AS IntervalStepsStart, DATEDIFF(D, @End, EndDate) + 1 AS IntervalStepsEnd, DATEDIFF(D, @Start, @End) + 1 AS PeriodSteps FROM vDataIsTakenHere WHERE ( (StartDate BETWEEN @Start AND @End) OR (EndDate BETWEEN @Start AND @End) OR (StartDate < @Start) AND (EndDate > @End) ) Now I still need to calculate the date differences to find out how many days each one of the rows have in a given period. Dunno if there is an easy way to do this, but this is how I was thinking to create it: 1) Calculate the date difference for each row (IntervalSteps) 2) Calculate the difference for StartDate and given period start date (IntervalStepsStart) 3) –^ Same for end date 4) Finally the total number of days in given period Then I would need to handle all the 4 cases as follows: 1) Case 1 (see row1 in original posting) IF (IntervalStepsStart > 0) AND (IntervalStepsEnd < 0) THEN NumberOfDaysInPeriod = IntervalSteps – IntervalStepsStart END 2) Case 2 IF (IntervalStepsStart < 0) AND (IntervalStepsEnd < 0) THEN NumberOfDaysInPeriod = IntervalSteps END 3) Case 3 IF (IntervalStepsStart < 0) AND (IntervalStepsEnd > 0) THEN NumberOfDaysInPeriod = PeriodicSteps + IntervalStepsStart END 4) Case 4 IF (IntervalStepsStart >= 0) AND (IntervalStepsEnd >= 0) THEN NumberOfDaysInPeriod = PeriodicSteps END So .. as it seems it becomes pretty confusing. That’s why the original question: is there a function in SQL that could do this trick?

CASE —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

Probably not the most optimized solution, but works smoothly <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Thanks for help guys! SELECT *, CASE IntervalType WHEN 1 THEN IntervalSteps-IntervalStepsStart WHEN 2 THEN IntervalSteps WHEN 3 THEN PeriodSteps+IntervalStepsStart WHEN 4 THEN PeriodSteps ELSE 1 END DaysWithinPeriod FROM ( SELECT *, IntervalType = CASE WHEN (IntervalStepsStart >= 0) AND (IntervalStepsEnd <= 0) THEN 1 WHEN (IntervalStepsStart <= 0) AND (IntervalStepsEnd <= 0) THEN 2 WHEN (IntervalStepsStart <= 0) AND (IntervalStepsEnd >= 0) THEN 3 WHEN (IntervalStepsStart >= 0) AND (IntervalStepsEnd >= 0) THEN 4 END FROM ( SELECT *, DATEDIFF(D, StartDate, @Start) AS IntervalStepsStart, DATEDIFF(D, @End, EndDate) + 1 AS IntervalStepsEnd, DATEDIFF(D, @Start, @End) + 1 AS PeriodSteps FROM vDataIsTakenHere WHERE ( (StartDate BETWEEN @Start AND @End) OR (EndDate BETWEEN @Start AND @End) OR (StartDate < @Start) AND (EndDate > @End) ) ) Dataset1 ) Dataset2

Using CASE is still the most direct option for getting the IntervalType … SELECT
(CASE WHEN (StartDate >= @Start) AND (EndDate <= @End) THEN 1
ELSE CASE WHEN (StartDate <= @Start) AND (EndDate BETWEEN @Start AND @End) THEN 2
ELSE CASE WHEN (StartDate BETWEEN @Start AND @End) AND (EndDate <= @End) THEN 3
ELSE CASE WHEN (StartDate BETWEEN @Start AND @End) AND (EndDate > @End) THEN 4 END END END END) IntervalType
FROM vDataIsTakenHere Look: no DATEDIFF in sight!

Well, not exactly those criteria, but you should get the point.[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

]]>