Be aware that Karen’s solution works for the most part, but watch your January dates. Since the Year/Month combination becomes text, subtracting 1 from a 01 month will return 00. So…: 200301 becomes 200300.
Whenever possible, you will want to retain your dates as date fields until the last possible second (before converting). It would be better to convert your date into another date, subtract the appropriate days and then turn to a string:
select to_char( to_date( to_char(to_date('31-DEC-2003'),'YYYYMM')||'01','YYYYMMDD') -1, 'YYYYMM') from dual;
This takes my string and turns it to a date… in this case using the YYYYMM portion and then adding a 01 to make it the first of the month. I then subtract one day (bringing me into November of 2003). Once I’m in November, I reformat it to a string.
This contains extra TO_DATES, but you should be able to break this down.
This will work except for January. In January of 2003, the sysdate expression would resolve to 200300, which is not valid. I would suggest using the add_months() function instead, as in:
sum(case when to_char(crt_dt,'YYYYMM')=to_char(add_months(sysdate, -1),'YYYYMM') then 1 else 0 end)
What’s the type/format of your Object? If it’s a date Object, I’d think it would still include the timestamp if your format is set to display the timestamp.
Date objects always have time stamp associated to them. You can, however, create a Custom List of Values to displays the values as character strings without the time stamp.
Select to_char( table.col, ‘mm/dd/yyyy’)
Order by trunc( table.col )
This should display a string for the LOV without changing how the object is actually handled.