But let's address the flaw in the original logic too
Dave, December 18, 2007 - 10:41 am UTC
As Tom showed, the TO_CHAR expression you use returns these values:
APRIL 2007
AUGUST 2007
DECEMBER 2007
FEBRUARY 2007
JANUARY 2007
JULY 2007
JUNE 2007
MARCH 2007
MAY 2007
NOVEMBER 2007
OCTOBER 2007
SEPTEMBER 2007
Note that these are fixed-length with spaces filling in between the end of the month name and the beginning of the year. The MONTH format element works this way.
Note also that September is the only one where there is a single space between the month name and the year. You said your query only works for "3 months ago", which would be September.
So the problem with your query is that you are trying to match values like these, that simply aren't equal as strings:
'NOVEMBER 2007' <> 'NOVEMBER 2007'
This support Tom's point, I think ... one of the flaws of representing dates as text is that you have to be very careful about formatting issues.
(That said, I work with a lot of legacy data that uses YYYYMM format for storing monthly numbers and we manage OK :)
December 18, 2007 - 2:03 pm UTC
well, the spaces are easy to change if they are a problem (I presumed the MONTH YYYY worked for them - they seemed to say it did)
ops$tkyte%ORA9IR2> with data as (select level-1 l from dual connect by level <= 12 )
2 select to_char(add_months(trunc(sysdate,'y'),l), 'fmMONTH YYYY') from data
3 order by 1
4 /
TO_CHAR(ADD_MO
--------------
APRIL 2007
AUGUST 2007
DECEMBER 2007
FEBRUARY 2007
JANUARY 2007
JULY 2007
JUNE 2007
MARCH 2007
MAY 2007
NOVEMBER 2007
OCTOBER 2007
SEPTEMBER 2007
12 rows selected.
briefly worked
Michelle Schweitzer, January 07, 2008 - 11:15 am UTC
I used the solution you provided but for some reason I had to us 10 instead of 1. But now that the month has changed again I can't make it work no matter what number I use. I am trying to figure out if there is another way to figure this.
January 07, 2008 - 11:27 am UTC
need more information - provide full example please. Not sure what "can't make it work" means.
Ugh....
Bill B, January 08, 2008 - 12:35 pm UTC
Never, ever store a date as a character string in the database. In my 25 years of programming, this is just a very bad idea. That being said, try the following.
[code]
SELECT SUBSTR (num_U, 1, 5),
SUM ((CASE
WHEN day.late <= 30
THEN 1
ELSE 0
END)
) on_time,
COUNT (num_U) total_pays
FROM schema.table
WHERE to_date(month_c,'MONTH YYYY') = trunc(add_months(SYSDATE, -2),'month');
[/code]
-l instead of l.
Kashif, January 10, 2008 - 2:33 pm UTC
I think the problem still exists because the code Tom suggested is adding months, instead of substracting months to go back in time (which is what the poster needed):
with data as (select level-1 l from dual connect by level <= 12 )
select to_char(add_months(trunc(sysdate,'y'),l), 'fmMONTH YYYY') toms_date,
to_char(add_months(trunc(sysdate,'y'),-l), 'fmMONTH YYYY') my_date
from data
order by l
Kashif
date columns...
A Reader, November 13, 2008 - 10:31 pm UTC
Tom,
I have table T
SQL> desc t;
Name Null? Type
-------------- -------- ------
OBJECT_NAME VARCHAR2(30)
CREATED DATE
SQL>
with data as follows.
OBJECT_NAME CREATED
T 14/11/2008 01:16:12
P 14/11/2008 19:09:17
SHOW_SPACE 06/11/2008 23:59:59
T2 06/10/2008 00:00:01
T1 29/10/2008 23:59:43
BIG_TABLE 03/08/2008 18:47:12
I want to select the object_name which are created between 18:00:00 hrs & 23:59:59 hrs in any day. How to do that?
November 18, 2008 - 5:40 pm UTC
where to_number(to_char(dt,'hh24miss')) between 180000 and 235959;