1

I have a list of periods during a year, and they are the same every year. You can think of it as a Season. They have a startDate and a endDate.

Because there can be Seasons that leap each other, what I need to to is query all the matching Seasons given a date, no matter what year.

As an example:

Season1: from 1st of January to 10th of January Season2: from 6th of January to 8th of January Season3: from 11th of January to 20th of January

Given the date 7th of January, I'd need to retrieve the Season1 and Season2.

I've tried converting all dates to the same year, but It doesn't work when the Start Date of a season in "later" than the End Date (for example, there's a period starting on November and ending of February).

Thanks in advance for the help.

Edit, sample data:

StartDate EndDate SeasonId 2000-08-01 2000-08-31 4 2000-12-29 2000-01-02 3 2000-06-01 2000-07-30 3 2000-09-01 2000-09-30 3 2000-01-06 2000-01-08 3 2000-04-07 2000-04-17 3 2000-04-28 2000-05-01 3 2000-06-02 2000-06-05 3 2000-06-23 2000-06-25 3 2000-09-08 2000-09-11 3 2000-09-22 2000-09-25 3 2000-10-12 2000-10-15 3 2000-11-01 2000-11-05 3 2000-12-01 2000-12-10 3 2000-12-22 2000-12-26 3 2000-03-01 2000-05-31 2 2000-10-01 2000-10-31 2 2000-11-01 2000-02-28 1

And I'd need, for example, the season for the date 2000-02-08, and retrieve seasonId = 1, or the date 2000-10-13and retrive seasonId = 3, seasonId = 2

Improve this question
5
  • 4
    give sample data Commented Mar 14, 2017 at 11:31
  • 2
    Specify sample data and expected result Commented Mar 14, 2017 at 11:32
  • See meta.stackoverflow.com/questions/333952/… Commented Mar 14, 2017 at 11:40
  • Does your data actually look like this? All from the same year and 2000-11-01 2000-02-28 1 is supposed to be from november to february? Commented Mar 14, 2017 at 11:50
  • Yes @dnoeth, but the thing here is that the year should not matter Commented Mar 14, 2017 at 11:54

4 Answers 4

Reset to default
1

I would do it in 2 'options': (the following SQL assumes you already got rid of the year in the table, and left only month-date format. )

 select ... from seasons s where (s.startDate <= s.endDate and s.startDate <= @mydate and s.endDate >= @mydate) or (s.startDate > s.endDate and s.startDate >= @mydate and s.endDate <= @mydate)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

You could query like this for the Season1:

select * from myTable where (month(myDate) = 1 and DAY(myDate) between 1 and 10)

If you have a season in more than one month, like start date January 20th, and finish date Febrery 10th, you could query this way:

select * from myTable where (month(myDate) = 1 and DAY(myDate) >= 20) or (month(myDate) = 2 and DAY(myDate) <= 10)

UPDATED WITH YOUR UPDATE

It is a little bit tricky, but it should work...

select * from seasons_table where cast(cast(day(myDate) as char) + '/' + cast(month(myDate) as char) + '/' + '2000' as date) between cast(cast(day(StartDate) as char) + '/' + cast(month(StartDate) as char) + '/' + '2000' as date) and cast(cast(day(EndDate) as char) + '/' + cast(month(EndDate) as char) + '/' + '2000' as date)
Improve this answer

Comments

1

given tblSeason with columns Id, startdate, enddate and your date as @myDate you would query as

Select Id From tblSeason WHERE @myDate BETWEEN startdate AND enddate

would give list of Id's of the seasons that match.

if you can't work from that, please give more information in your examples as to the structure you are querying and the expected outcome. *Edit to ignore the year part you could do similar to

 Declare @myDate datetime = '2016-10-13' SELECT [StartDate] ,[EndDate] ,[SeasonId] FROM [dbo].[Table_1] where DATEPART(dy, @myDate) >= DATEPART(dy,StartDate) AND (DATEPART(dy,@myDate) =< DATEPART(dy,EndDate) OR DATEPART(dy,StartDate) > DATEPART(dy,EndDate))
Improve this answer

2 Comments

The thing here is that the year is irrelevant, for example, I've got a period going from November to February, and using your method the date 10th of December wouldn't work
yeah sorry I saw your edit after I had posted will edit answer
1

Why are you including the year in the table? That seems strange.

In any case, you only care about the MM-DD format, so use date_format() to convert the values to strings:

select t.* from t where (start_date <= end_date and date_format(@date, '%m-%d') >= date_format(start_date, '%m-%d') and date_format(@date, '%m-%d') <= date_format(end_date, '%m-%d') ) or (start_date > end_date and date_format(@date, '%m-%d') <= date_format(start_date, '%m-%d') and date_format(@date, '%m-%d') >= date_format(end_date, '%m-%d') );

The strings are fine for comparison, because you are only looking at the month and day components of the date.

Given the nature of your problem, I would recommend that you store start_date and end_date in a non-date format, such as MM-DD.

Improve this answer

6 Comments

@SagarGangwal . . . Comparing strings is fine, if that is the intention of the code. In this case, with no year component, converting to a date seems a much, much, much worse solution.
@SagarGangwal . . . You seem very poorly informed. This code converts the dates to strings in the MM-DD format. The comparisons are correct. You will note that the date parts are formatted with leading zeroes, which is why they are correct. Do you know what MM-DD is? Do yo know what '%m-%d' does in MySQL? And, as for the example in your comment, it is correct to return 1, because November 11th is "bigger" than January 12th.
@SagarGangwal . . . I am cutting off this discussion. You clearly do not know the difference between dates and strings and hence do not understand this logic, even after it has been explained both in the answer itself and in the comments.
@SagarGangwal: Yep, you're totally wrong, of course 11-11 (=November 11) is greater than 01-12 (January 12) because you compare MM-DD instead of DD-MM.
Well, I finally went with @GordonLinoff answer. I had problems in periods of time like 12-01 to 31-01, comparing which of the dates was greater than the other. Finally, I've modified the data to not include periods of time where the startDate belongs to a different "year" than the endDate. I also converted all the period dates to string, and removed the year on them.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.