filter from start of the year

Toad for Oracle
1

I’m trying to create a filter using a varchar2 field, report_period, which is displayed 201401 (YYYMM) and sysdate, see below

select report_period, sum(issue_cnt)
from my_mis
WHERE report_period >= to_char(trunc(sysdate, ‘YYYY’))
group by report_period

the query will run but the filter is not recognized and i return all ‘YYYYMM’ in table… it starts in 2011. Any ideas on how it will run but not recognize the filter and any thoughts on correcting this?

cheers

Jefre

2

filter from start of the year

Thread created by jepotter

I’m trying to create a filter using a varchar2 field, report_period, which is displayed 201401 (YYYMM) and sysdate, see below

select report_period, sum(issue_cnt)

from my_mis

WHERE report_period >= to_char(trunc(sysdate, ‘YYYY’))

group by report_period

the query will run but the filter is not recognized and i return all ‘YYYYMM’ in table… it starts in 2011. Any ideas on how it will run but not recognize the filter and any thoughts on correcting this?

cheers

Jefre

	 To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

–

Sent from my Android device with K-9 Mail. Please excuse my brevity.

3

Not sure where my reply went!

Try comparing to_date (report_period, 'YYYYMM') with to_date (to_char (sysdate, 'YYYY') || '01', 'YYYYMM') in the where clause. You need to compare dates, not strings.

Your database designer should be chastised for allowing dates to be stored in a character data type. It may also be causing the optimiser some grief in picking a decent plan.

Have fun.

Cheers,

Norm. [ TeamT ]

On 24 October 2014 23:17:29 BST, "Norm [TeamT]" bounce-NormTeamT@toadworld.com wrote:

filter from start of the year

Reply by Norm [TeamT]
filter from start of the year

Thread created by jepotter

I'm trying to create a filter using a varchar2 field, report_period, which is displayed 201401 (YYYMM) and sysdate, see below

select report_period, sum(issue_cnt)
from my_mis
WHERE report_period >= to_char(trunc(sysdate, 'YYYY'))
group by report_period

the query will run but the filter is not recognized and i return all 'YYYYMM' in table.... it starts in 2011. Any ideas on how it will run but not recognize the filter and any thoughts on correcting this?

cheers

Jefre

To reply, please reply-all to this email.

Stop receiving emails on this subject.
Or Unsubscribe from Toad for Oracle - General notifications altogether.
Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

--
Sent from my Android device with K-9 Mail. Please excuse my brevity.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

--

Sent from my Android device with K-9 Mail. Please excuse my brevity.

4

I’m not sure what happened to my text in that reply, it seems to have vanished, never mind, try again.
I think your problem is caused by the fact that you are storing DATEs in a character column rather than in a DATE column. This needs fixing but in the meantime, you should/must compare the data as dates and not as characters.
Try the following in your where clause, and note the messing about with the SYSDATE to get it into a date from character from date, to do a correct comparison.
WHERE to_date(report_period, ‘YYYYMM’) >= to_date(to_char(sysdate, ‘YYYY’) || ‘01’)
In addition to your problem, the use of character dates rather than DATE dates may also be affecting the cost based optimiser (or optimizer of you like!) in certain cases.
I think your query is returning all the rows because a 6 character string is usually greater than a 4 character string. (I think, I’m not at a database just now!)
– Cheers,
Norm. [TeamT]

5

After three times reading have no clue what do you want!?

:slight_smile:

But maybe this may help you. Please be aware that this is not state of the art codding (as your column in varchar used as dates is as well) but should helps:

alter session set nls_date_format=‘yyyymm’;

and then modify your query where part:
WHERE to_date(report_period) >= to_date(to_char(sysdate, ‘yyyy’)||‘01’||‘01’,‘yyyymmdd’)

Hope this helps
Damir

6

The only thing actually wrong with your query is that you have a bracket in the wrong place.

Where you have to_char(trunc(sysdate, ‘YYYY’)) it should be to_char(trunc(sysdate), ‘YYYY’)

With the bracket where it is, sysdate will be truncated to the year, but then the TO_CHAR will return the default string (‘JAN-01-2014’ for a default US NLS setting), and then will compare that string to your ‘201101’ string.

7

Thank you all. After looking at your suggestions I realized it’s easier to just concat the sysdate. Norm got the idea from you first. a simple fix, maybe too easy and just over looked it. so i ended up with

select report_period, sum(issue_cnt)

from my_mis

WHERE report_period >= to_char(sysdate,

‘YYYY’) || ‘01’

group by report_period

just in case anyone was wondering!

8

WHERE report_period >= to_char(sysdate, 'YYYY') || '01'

I would never leave varchar2 values in where condition which in fact has to be compared as date-never! never!

I have seen many assumptions on column types like this one and this assumption leads to very strange result .... This will happened some day...sooner or later. But will one day.