CODEWITHSUNDEEP
sqloracle-database
user3009274
user3009274

Reputation: 1

Specific day- sql oracle

How can I find a specific day no matter the year or date in sql oracle. as an example i have the folowing dates in and table id date 1 2013-02-03 2 2013-01-04 3 2012-06-13

I want to find all the ids with the day= 13

I've did this already but it doesn't work

select id
from table
where dayofmonth(date) = 13

this works in mysql but not in oracle. Can any of you help me?

Upvotes: 0

Views: 148

Answers (1)

Frank Schmitt
Frank Schmitt

Reputation: 30775

You can either use to_char() + to_num()

select to_number(to_char(sysdate, 'DD')) from dual

or use the extract() function:

select extract (day from sysdate) from dual

EDIT:

To get all rows from a table whose day is 13, just use EXTRACT in the WHERE clause:

create table my_data as
(select 1 pk, to_date('2013-01-13', 'YYYY-MM-DD') as my_date from dual union all
 select 1 pk, to_date('2013-01-14', 'YYYY-MM-DD') as my_date from dual union all
 select 1 pk, to_date('2013-02-13', 'YYYY-MM-DD') as my_date from dual);

select * from my_data
where extract(day from my_date) = 13;

Upvotes: 2

Related Questions