CODEWITHSUNDEEP
sqloracle-database
cdoe
cdoe

Reputation: 469

How to use bind variables for date literals?

Consider the following SQL

SELECT * FROM employee WHERE dob = DATE '1980-05-15'

I need to rewrite the query to get rid of the hard coded values and use bind variables instead. So, my rewritten query looks like this

SELECT * FROM employee WHERE dob = DATE :dateOfBirth

However, the query doesn't work. I've tried all possible formats (1980-05-15, 15-MAY-80, 15-MAY-1980) for :dateOfBirth variable, without any luck. I always get ORA-00936: missing expression error.

Note 1: I'm aware of to_date() function that can solve my issue, but I can't use that because the query originates from a different system on which I don't have any control over.

Note 2: DD-MON-RR is the nls_date_format in my database as specified in nls_session_parameters & nls_database_parameters

Upvotes: 2

Views: 474

Answers (1)

Chris Saxon
Chris Saxon

Reputation: 9825

You can't, you need to to_date it:

SELECT * FROM employee WHERE dob = to_date ( :dateOfBirth, '<fmt>' );

Upvotes: 2

Related Questions