• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1194
  • Last Modified:

Static date range every Month needed using YYYYMMDD Varchar 'date' format

Hi,
I have an indexed CHAR field that is fully populated in the YYYYMMDD format and is used as the basic date range for reports. So, "select a,b,c from tablex where dt_field >= '20081014'  " would retrieve rows from 10/14/2008, and so on. For Ongoing reports for last Month we use this format: dt_field BETWEEN to_char(last_day(add_months(sysdate, -2)) + 1,'YYYYMMDD') AND to_char(last_day(add_months(sysdate, -1)),'YYYYMMDD')  ) . That Works fine.

But I have an ongoing request to view data FROM the 21st of the PREVIOUS Month to the 20th of the CURRENT month (i.e 10/21/08- 10/20/08 but for every month).  The idea is to have some sort of flexible hardcode to work with. The field is an indexed field but I can use another field to subset the query in the where clause to speed things up a bit.

Thanks very Much,
Doug


0
dougc11
Asked:
dougc11
  • 7
  • 6
  • 3
1 Solution
 
grzessioCommented:
i would use
for 21st of last month and 20th of this month
between 
to_char(trunc(add_months(sysdate, -1), 'yyyymm') + 21, 'yyyymmdd')
and 
to_char(trunc(sysdate, 'yyyymm') + 20, 'yyyymmdd')
 
btw why don`t you use dates instead of chars? 

Open in new window

0
 
dougc11Author Commented:
Hi,
Let me test this out. This looks like an simple/elegant solution.
The CHAR field is part of a nightly cycle and pre-dates me. I have no room to work with on that part.
Thanks again.
-Doug
0
 
dougc11Author Commented:
The error I get is: "ORA-01898: too many precision specifiers"
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Dr_BillyCommented:
use this

Between
to_char(To_Date('21-'||to_char(add_months(sysdate,-1),'MM-YYYY'),'DD-MM-YYYY'),'YYYYMMDD') 
AND
to_char(to_date('20-'||to_char(sysdate,'MM-YYYY'),'DD-MM-YYYY'),'YYYYMMDD') 

Open in new window

0
 
grzessioCommented:
ok. I`m sorry trunc goes with only one precision specifier. it should be:

to_char(trunc(add_months(sysdate, -1), 'mm') + 20, 'yyyymmdd')
,
to_char(trunc(sysdate, 'mm') + 19, 'yyyymmdd')

Open in new window

0
 
dougc11Author Commented:
Can you walk me through this logic please?
0
 
dougc11Author Commented:
Question : If this WAS an Oracle date field what would the logic be? There are other requests that come in and an oracle date is used.
Thanks Again.
0
 
grzessioCommented:
add_months(sysdate, -1) <- this is the same day as today but in previous month

trunc(add_months(sysdate, -1), 'mm') <- this is first day of previous month

trunc(add_months(sysdate, -1), 'mm') + 20 <- this is 21st day of previous month

similarly in the second statement.
0
 
grzessioCommented:
why I asked about date fields? well you are using dates converted to chars. if someone makes a mistake and instead of yyyymmdd will use ddmmyyyy 'between' funcionality will stop working. For dates it will work every time.
0
 
grzessioCommented:
so whole between should be:
between 
to_char(trunc(add_months(sysdate, -1), 'mm') + 20, 'yyyymmdd')
and
to_char(trunc(sysdate, 'mm') + 19, 'yyyymmdd')

Open in new window

0
 
dougc11Author Commented:
Can you show me the oracle date version of the same range? I increased the point range for your effort.
0
 
grzessioCommented:
if you want to compare date it would look like:

<some date field>
between 
trunc(add_months(sysdate, -1), 'mm') + 20
and
trunc(sysdate, 'mm') + 19
 
for me it is more natural.

Open in new window

0
 
Dr_BillyCommented:
Did you try my post ?
0
 
dougc11Author Commented:
Yes, you both (Dr Billy & grzessio) answered my question correctly , But I'm not sure what to do about the points.
0
 
Dr_BillyCommented:
In this case you split the points
0
 
dougc11Author Commented:
thanks very much!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now