SQL Stored Procedure Convert Date Parameter.

Question

Hi,

I have an 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??

Thanks,

Tina

Answer 1

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');

Answer 2

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');