CODEWITHSUNDEEP
sqloracle-database
user10582844
user10582844

Reputation: 1

Compare String to Date

I have the procedure below where I want to convert string data into date and compare it with other dates.

The eev.screen_entry_value is a Varchar2 with values in the format "2018/10/26 00:00:00".

I want to convert this to date format and compare with other dates as below-

TO_DATE(to_date(eev.screen_entry_value,'YYYY/MM/DD HH24:MI:SS'),'DD-MON-YYYY') BETWEEN P_START_DATE AND P_END_DATE;

However, I'm getting an error-

ORA-01861: literal does not match format string

Below is my code snippet-

declare
 p_start_date DATE;
 p_end_date DATE;
 p_screen_entry_value varchar2(60);
BEGIN
 SELECT ptp2.start_date, ptp2.end_date
 into p_start_date, p_end_date
 FROM  per_time_periods ptp
 , per_time_periods ptp2
 , pay_payrolls_f pp
 WHERE pp.payroll_name LIKE 'US Semi-Monthly'
 AND TRUNC(sysdate) BETWEEN pp.effective_start_date AND pp.effective_end_date
 AND pp.payroll_id = ptp.payroll_id
 AND TRUNC(sysdate) BETWEEN ptp.START_DATE AND ptp.END_DATE
 AND ptp.payroll_id = ptp2.payroll_id
 AND ptp.end_date BETWEEN ptp2.start_date AND ptp2.end_date;
 dbms_output.put_line(p_start_date);
 dbms_output.put_line(p_end_date);

 SELECT eev.screen_entry_value
 into p_screen_entry_value
 FROM  pay_input_values_f piv,
 pay_element_types_f et,
 pay_element_links_f el,
 pay_element_entries_f ee, 
 pay_element_entry_values_f eev
 WHERE  
  ee.assignment_id IN (364018)
  AND ee.element_link_id = el.element_link_id
  AND sysdate  BETWEEN el.effective_start_date 
            AND el.effective_end_date
  AND ee.element_type_id = et.element_type_id
  AND sysdate BETWEEN et.effective_start_date
            AND et.effective_end_date
  AND et.attribute_category = 'US Elements'
  AND et.attribute1 = 'Y' --Interface to ProBusiness
  AND ee.element_entry_id = eev.element_entry_id
  AND sysdate  BETWEEN eev.effective_start_date
            AND eev.effective_end_date
  AND eev.input_value_id = piv.input_value_id
  AND sysdate  BETWEEN piv.effective_start_date
            AND piv.effective_end_date
  AND eev.screen_entry_value IS NOT NULL  
  AND et.element_name IN ('HV Sign On Bonus','HV Retention  Bonus','Relocation')  --XX_HR_NONREC_ELEMENTS
  AND piv.name IN('Vested 1 Date');
 --AND TO_DATE(to_date(eev.screen_entry_value,'YYYY/MM/DD HH24:MI:SS'),'DD-MON-YYYY') BETWEEN P_START_DATE AND P_END_DATE;
 dbms_output.put_line(p_screen_entry_value);

 end;

Upvotes: 0

Views: 137

Answers (1)

Alex Poole
Alex Poole

Reputation: 191580

Doing to_date(to_date(...)) doesn't really make sense. The inner one converts the string to a date, then the outer one tries to convert that date to... a date. To try to do that it has to implicitly convert the date back to a string again, and it will use your session's NLS_DATE_FORMAT for that implicit step.

If you wanted to get it back to a string in a specific format you would use to_char() for the outer call:

to_char(to_date(eev.screen_entry_value,'YYYY/MM/DD HH24:MI:SS'),'DD-MON-YYYY') ...

But the values you are comparing against are dates, so you don't need or want that second conversion at all:

to_date(eev.screen_entry_value,'YYYY/MM/DD HH24:MI:SS')
BETWEEN P_START_DATE AND P_END_DATE

Upvotes: 2

Related Questions