Filter By date with format 'dd/mm'

Question

I have difficulty in syntax query dbms oracle for filtering by date, but i just want filter by date with format (dd/mm). and my data database record is (dd/mm/yyyy). How query to filter by date mm/dd

tablename.fieldname >= '01/01'

Please provide sample data and desired results. I don't understand how `>=` would be used in this case. — Gordon Linoff, May 15 '19 at 11:50

score 2 · Answer 1 · answered May 15 '19 at 06:32

2

with s as (
select date '2019-01-01' + level * 15 dt from dual connect by level <= 20)
select s.*
from s
where to_number(to_char(dt, 'mmdd')) > 0501;

DT                 
-------------------
2019-05-16 00:00:00
2019-05-31 00:00:00
2019-06-15 00:00:00
2019-06-30 00:00:00
2019-07-15 00:00:00
2019-07-30 00:00:00
2019-08-14 00:00:00
2019-08-29 00:00:00
2019-09-13 00:00:00
2019-09-28 00:00:00
2019-10-13 00:00:00
2019-10-28 00:00:00

12 rows selected.

answered May 15 '19 at 06:32

Amir Kadyrov

1,253
4
9

Not work master, they still showing month > 01 with s as( select date '2019-01-01' + level * 15 dt from dual connect by level <= 20) select s.* from s where to_number(to_char(dt, 'ddmm')) < 0201; DT 01/04/2019 01/05/2019 – Dimas Prayoga May 15 '19 at 06:40
I am sorry, i have edit your code to ddmm, please look my comment code. master – Dimas Prayoga May 15 '19 at 06:56
i want filter by dd/mm for birthday filter – Dimas Prayoga May 15 '19 at 06:56
@DimasPrayoga you need to filter by mmdd in order to get the results, due to ordering. `'15/12'` is less than `'23/01` despite the 15th of December being later than 23rd of January. You have to sort by the month first, then the day. – Boneist May 15 '19 at 07:20
@Boneist i have dufficult for realitation that code – Dimas Prayoga May 15 '19 at 07:23
@DimasPrayoga why? You mean you absolutely cannot pass the parameter as `mmdd`? (If so, you'd have to convert the string into `mmdd` first, which isn't difficult.) Or is there some other reason? – Boneist May 15 '19 at 07:25

score 1 · Answer 2 · answered May 15 '19 at 07:28

Have you tried something like this? Though it's not exactly the format you wanted

SELECT * from yourTableName 
WHERE EXTRACT(month from yourDateColumn) >= '1'
AND EXTRACT(day from yourDateColumn) >= '1';

Oracle EXTRACT documentaion

Possible related questions:

Compare only day and month with date field in mysql

SQL Filter Out Specific Month, Day, and Time for Any Year

Filter By date with format 'dd/mm'

2 Answers2