The sample from the database by date using only the day and month. Not taking into account the year

Question

I have a database, let's call it "mydb". The database "mydb", there is a table "mytable". In the table "mytable", three fields: text (type char), start_date (type DATE) and end_date (type DATE).

I want to make a selection for a specific date range from start_date to end_date. But I would want that sampling was only days and months, not including the year! How can this be done?

Particularly interested in the question how to make a selection between:

23.12 - 01.01

If I enter 01/01/2013, this date must fall within a range!

I use PostgreSQL.

Surely using to_date(start_date, 'DDD') instead of dayofyear(startdate) is a trivial difference. Update that question/answer with this addition if you want to be complete. That would be better than asking the same question again. — mdahlman, May 18 '13 at 12:37
The concepts 'between' and 'range' don't apply when the values aren't on the *timeline*. Conceptualizing it as 'between' is throwing you off. You're posing the question in natural language terms imprecisely. Say you were looking at data for home heating costs in the northern hemisphere for the last 50 years, and wanted all purchases of diesel fuel oil made "between" November 15th and Februrary 15th for all years. In natural language terms you want to pose the question like this: I am looking for purchases that occurred on or after November 15th OR that occurred on or before February 15th. — Tim, May 18 '13 at 12:44

score 0 · Answer 1 · answered May 18 '13 at 09:58

0

You can use date_part() to extract part of the date.

SELECT foo FROM bar WHERE
(date_part('month', foo)=12 AND date_part('day', foo)>=23)
OR
(date_part('month', foo)=1 AND date_part('day', foo)=1)

http://sqlfiddle.com/#!12/7123d/1

answered May 18 '13 at 09:58

Jakub Kania

15,665
2
37
47

Thank you! But you can see a working example of the solution of my question with this. – user2396461 May 18 '13 at 12:27

score -1 · Answer 2 · answered May 18 '13 at 10:52

Check this

create table mytable(inputDate text, start_date DATE,end_date DATE);

insert into mytable values('2013-01-01',to_date('2013-01-01', 'YYYY-MM-DD'),to_date('2013-12-23', 'YYYY-MM-DD'));

insert into mytable values('2014-01-01',to_date('2013-01-01', 'YYYY-MM-DD'),to_date('2013-12-23', 'YYYY-MM-DD'));

insert into mytable values('2013-12-24',to_date('2013-01-01', 'YYYY-MM-DD'),to_date('2013-12-23', 'YYYY-MM-DD'));

insert into mytable values('2014-12-24',to_date('2013-01-01', 'YYYY-MM-DD'),to_date('2013-12-23', 'YYYY-MM-DD'));

Query to check range ignoring Year

select * from mytable
where 
( 
  date_part('month',to_date(inputDate,'YYYY-MM-DD')) >=date_part('month',start_date)
  AND
  date_part('day',to_date(inputDate,'YYYY-MM-DD')) >=date_part('day',start_date)
 )

AND
 (
   date_part('month',to_date(inputDate,'YYYY-MM-DD')) <=date_part('month',end_date)
  AND
  date_part('day',to_date(inputDate,'YYYY-MM-DD')) <=date_part('day',end_date)
  )

SQL FIDDLE DEMO

Try this with the dates he said. The input date is Jan 1. The start date is Dec 24 and the end date is Jan 1. — Dan Bracuk, May 18 '13 at 11:35
At your option expiry date (end_date) always later (more) than the start date (start_date). In my version, the date of the beginning 23.12 of the end date later 01.01. How to be in this case? — user2396461, May 18 '13 at 12:23

The sample from the database by date using only the day and month. Not taking into account the year

2 Answers2