CODEWITHSUNDEEP
sqloracle-databasedate
Leo S
Leo S

Reputation: 339

comparing date in pl/sql

I want to code a function that take a year as parameter and return the person that born in that year. But my people table format like 1993-01-06 00:00:00 so how can i compare date and return the filtered person in sql?

Upvotes: 0

Views: 48

Answers (2)

MT0
MT0

Reputation: 167832

Assuming that your table has a column with the DATE data type then:

SELECT *
FROM   table_name
WHERE  date_column >= TO_DATE( :year || '-01-01', 'YYYY-MM-DD' )
AND    date_column <  TO_DATE( ( :year + 1 ) || '-01-01', 'YYYY-MM-DD' );

(Use a range of dates rather than using EXTRACT or TO_CHAR on your date column as this allows Oracle to use an index on the date column.)

If your column has a string data type with a 4-digit year:

SELECT *
FROM   table_name
WHERE  string_column LIKE :year || '-%'

(Don't just concatenate % as, if you have historical years then, trying to match the year 200 would also match the year 2000.)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269513

If the data type of a the column is a string, then you can use:

where col like :year || '%'

If the data type is a date or timestamp, then you can use:

where extract(year from col) = :year

Upvotes: 0

Related Questions