To_date formatting of date variable 1

[thiagarr]

Hi,

I have defined two variables as follows:

currentBeginDate := TO_date(ADD_MONTHS(LAST_DAY(ADD_MONTHS(sysdate, -1)),-1)+1, 'YYYY-MM-DD');
currentEndDate := TO_date(LAST_DAY(ADD_MONTHS(sysdate,-1)), 'YYYY-MM-DD');

testbegindate := to_date('2006-10-01','YYYY-MM-DD');
testenddate := to_date('2006-10-31','YYYY-MM-DD');

All the above are defined as varchar2(20).

I am running a query in a procedure, which has SELECT statements as follows:

1 SELECT COUNT(USER) INTO totalNewUserCount FROM UP_USER
WHERE DATE_CREATED BETWEEN testBeginDate AND testEndDate
AND CUSTOMERTYPE <> 'A';

2 SELECT COUNT(USER) INTO totalNewUserCount FROM UP_USER
WHERE DATE_CREATED BETWEEN to_date('2006-10-01','YYYY-MM-DD') AND to_date('2006-10-31','YYYY-MM-DD')
AND CUSTOMERTYPE <> 'A';

3 SELECT COUNT(USER) INTO totalNewUserCount FROM UP_USER
WHERE DATE_CREATED BETWEEN currentBeginDate AND currentEndDate
AND CUSTOMERTYPE <> 'A';

SELECT statements 1 and 2 above produce the correct result whereas the 3rd SELECT statement provide an incorrect result. I beleive this is because of the way the date is formatted. Unfortunately, I want the correct result using the 3rd option or an equivalent of the option (Crystal Report wants the date to be in this format.

Any idea how I should rewrite the definition of the variables currentBeginDate and currentendDate same as testbegindate and testenddate?

Any suggestion would be highly appreciated.

Thanks and regards,

Chandra

 

[BJCooperIT]

Both currentBeginDate and currentEndDate will carry the time element of sysdate.

SELECT TO_CHAR(SYSDATE,'yyyymmddhh24miss') now,
       TO_CHAR(to_date('20061121','yyyymmdd'),'yyyymmddhh24miss') tcd
FROM dual;

[b]NOW             TCD[/b]
20061121181806  20061121000000

This will affect what falls between. Try:

currentBeginDate :=
  TO_date(ADD_MONTHS(LAST_DAY(ADD_MONTHS([COLOR=red]trunc(sysdate)[/color], -1)),-1)+1, 'YYYY-MM-DD');

currentEndDate := 
  TO_date(LAST_DAY(ADD_MONTHS([COLOR=red]trunc(sysdate)[/color],-1)), 'YYYY-MM-DD');

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[thiagarr]

Hi,

Thank you for the response. I tried this and I am still getting the same error as before. Only when I change the formatting of currentBeginDate and currentendDate, as follows, it seems to work.

currentBeginDate :=
  TO_date(ADD_MONTHS(LAST_DAY(ADD_MONTHS(trunc(sysdate), -1)),-1)+1, '[COLOR=black yellow]DD-MM-YYYY[/color]');

currentEndDate := 
  TO_date(LAST_DAY(ADD_MONTHS(trunc(sysdate),-1)), [COLOR=black yellow]DD-MM-YYYY[/color]');



Thanks and regards,

Chandra

 

[karluk]

I'm glad you found a solution that works for you. However, for future reference you might be interested in why your original expression failed. It's because you are using the "to_date" function on a value that is already an Oracle date datatype. The Oracle 9i documentation explicitly warns against doing this:

Do not use the TO_DATE function with a DATE value for the char argument. The first two digits of the returned DATE value can differ from the original char, depending on fmt or the default date format.

You will also notice that you are still using "to_date" on date values in the expressions that work. Even though it turns out to be harmless, you could simplify the calculations and get the same results by omitting the "to_date" function completely:

currentBeginDate :=
  ADD_MONTHS(LAST_DAY(ADD_MONTHS(trunc(sysdate), -1)),-1)+1;

currentEndDate := 
  LAST_DAY(ADD_MONTHS(trunc(sysdate),-1));

 

[thiagarr]

Karluk,

Thank you for your response.

I tested this without to_date and it works well. Thank you for your help.

Thanks and regards,

Chandra