CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsql
Jacob
Jacob

Reputation: 73

SQL Stored Procedure Convert Date Parameter

I have a SQL stored procedure which accepts two dates, however when I send them in my open query, Oracle does not like the date format for some reason.

How can I change the dateformat to YYYY-MM-DD from dd-mm-yyyy in the stored procedure before sending using it.

e.g SET @startdate = CONVERT

Upvotes: 2

Views: 9667

Answers (2)

Pyae Phyoe Shein
Pyae Phyoe Shein

Reputation: 13827

You can use TO_CHAR function of Oracle.

/*retrieve from query*/
select TO_CHAR(reg_date,'YYYY-MM-DD') REGDATE from Users_TBL 

/*Assign to variable*/
regDate := TO_CHAR(reg_date,'YYYY-MM-DD');

Upvotes: -1

OMG Ponies
OMG Ponies

Reputation: 332691

Use the TO_DATE function to convert a string value into an Oracle DATE data type.

To accept a date string in the format YYYY-MM-DD:

v_start_date DATE := TO_DATE(v_date_string, 'YYYY-MM-DD');

To accept a date string in the format DD-MM-YYYY:

v_start_date DATE := TO_DATE(v_date_string, 'DD-MM-YYYY');

Upvotes: 5

Related Questions