Urgent : Help to convert string to date

[upriyaa]

Hi
My requirement is to compare a date in string format with the Sysdate. The problem is the date string does not contain a zero before the single digit date and month.

Here is my query

select * from table_name where to_date(%date%, 'MM/DD/YYYY) > (SYSDATE - 7);

this gives an error saying the month is not valid when the month in %date% is single digit.
Please help

Thanks

[amitpatel66]

Hi
My requirement is to compare a date in string format with the Sysdate. The problem is the date string does not contain a zero before the single digit date and month.

Here is my query

select * from table_name where to_date(%date%, 'MM/DD/YYYY) > (SYSDATE - 7);

this gives an error saying the month is not valid when the month in %date% is single digit.
Please help

Thanks

This error is because the NLS_DATE_FORMAT is something different for your session from the format that you are using.

Run the below query in your session before running the above select statement:

ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/RRRR';
 

[cheguvera]

Hi,

This may not look pretty but, if you run a query,

select ltrim(to_char(sysdate - 195,'DD'),'0') || '/' || ltrim(to_char(sysdate - 195,'MM'),'0') || '/' || to_char(sysdate - 195,'YYYY') from dual

you will see the system date in dd/mm/yyyy format without zeros.
All I have done is put a ltrim on to_char and trimmed the day and month saperately and then appended them.

You can use such string and directly equate with your date.

Regards

[susarlasireesha]

select * from emp
where to_date(to_char(sysdate,'mm/dd/yyyy'),'mm/dd/yyyy') > to_date(to_char(SYSDATE-7,'mm/dd/yyyy'),'mm/dd/yyyy');
Try this..................