Working out 6 month prior to the first day of the month

[SomaRich]

Hi!
I can work out the first day of the month using this code:

select 
   to_char(
      trunc(sysdate,'MM'),'DD/MM/YYYY'
   ) as First_Day
from dual;

But why does it not work when I plug it into my code?

select DUE_DATE from vw_current_task
where
DUE_DATE >  to_date(trunc(sysdate,'MM'),'DD/MM/YYYY') - 6
order by DUE_DATE asc

Sorry its probably a really simple one.

[Dave44]

there are a couple issues here. first you are using to_Date on a date, which will cause an error ( i suspect you want to use to_Char there to give you the nice formatted date). However if DUE_DATE is a date and not a string then you want to keep the first of the month as a date, and not convert it to a string.

keep your dates as date datatypes unless you are returning it to be viewed by a report and want it looking in a specific format. rarely ever in the where clause of a query should you be changing dates to strings for comparison, if need be convert the string to a date for the comparison, you are less likely to make an error this way compared to the other way.

select to_Date(sysdate,'yyyymmdd') from dual;
ORA-01858: a non-numeric character was found where a numeric was expected
 
select to_char(sysdate,'yyyymmdd') from dual;
 
TO_CHAR(
--------
20071205
 
select to_char(sysdate,'yyyymmdd') - 6 from dual;
 
TO_CHAR(SYSDATE,'YYYYMMDD')-6
-----------------------------
                     20071199
 

NOTE the following the last line there, this is why you want to be careful about comparing strings and not dates.
also you will want to use the add_months(<your_date>,-6) to get 6 months. as you have it written it will minus 6 days not months if it was in date format and not string (the string really screws things up here)

[amitpatel66]

Hi!
I can work out the first day of the month using this code:

select 
   to_char(
      trunc(sysdate,'MM'),'DD/MM/YYYY'
   ) as First_Day
from dual;

But why does it not work when I plug it into my code?

select DUE_DATE from vw_current_task
where
DUE_DATE >  to_date(trunc(sysdate,'MM'),'DD/MM/YYYY') - 6
order by DUE_DATE asc

Sorry its probably a really simple one.

Hi SomaRich,

Welcome to TSDN!!

Please make sure you follow POSTING GUIDELINES every time you POST in this forum

Thanks
MODERATOR