CODEWITHSUNDEEP
sql
Codeek
Codeek

Reputation: 1624

finding records within date range irrespective of year

I want to find records within a date range say between 15th August and 15th December, irrespective of the year. So basically I want all records from month 8 to 12 and none from 1st to 14th day of 8th month and and 16th to 31st day of 12th month.

I already can do this in two ways

  1. by using multiple conditions using "and/or" or cases in where clause.
  2. by using day no. of the year (ofcourse handling leap years).

I just want to know if there is any other or better way like one line function of formula something

I tried to keep the question explanation as simple as possbile for me. Still if you need it I'll post the explanation using tabular data too. (I'll have to create it though.)

Thanks in advance to all the people who took their time to read and consider the question.

Upvotes: 0

Views: 97

Answers (3)

David Dubois
David Dubois

Reputation: 3932

Would you be looking for something like this? The idea is to transmute every date into the same year, in this case, year 1. To do that, subtract the appropriate number of years. 

create table MyTable ( MyDate date )

insert into MyTable ( MyDate ) values
( '1889-08-14' ), ( '1891-08-15' ), ( '1899-12-30' ),
( '2018-04-04' ), ( '2018-08-14' ), ( '2018-08-15' ),
( '2018-09-10' ), ( '2018-09-20' ), ( '2018-12-15' ),
( '2018-12-16' ), ( '2019-08-11' ), ( '2019-08-21' ),
( '2019-09-22' ), ( '2019-12-03' ), ( '2019-12-31' ),
( '2020-01-01' ), ( '2030-08-14' ), ( '2040-08-15' ),
( '2050-12-15' ), ( '2060-12-16' ), ( '2222-09-23' )

select * From MyTable 
where DateAdd ( year, 1-Year(MyDate), MyDate ) between '0001-08-15' and '0001-12-15'

Result is:

MyDate
----------
1891-08-15
2018-08-15
2018-09-10
2018-09-20
2018-12-15
2019-08-21
2019-09-22
2019-12-03
2040-08-15
2050-12-15
2222-09-23

If you feel uncomfortable with referencing year 1, you could make it any year.

where DateAdd(year, 2000-Year(MyDate), MyDate)between'2000-08-15'and'2000-12-15'

In MYSQL this would look something like:

where DATE_ADD(MyDate, INTERVAL 1-year(MyDate) YEAR);

Upvotes: 0

Ravi Kanex
Ravi Kanex

Reputation: 1

Using Sql DATEDIFF

SELECT DATEDIFF (d,'2018-08-15','2018-12-16')

Upvotes: -1

Strawberry
Strawberry

Reputation: 33945

It's not particularly efficient, but I would expect to see something like this:

SELECT * 
  FROM my_table
 WHERE DATE_FORMAT(dt,'%m-%d') BETWEEN '08-15' AND '12-14' 
 ORDER 
    BY dt;

Upvotes: 3

Related Questions