?
Solved

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

Posted on 2008-10-14
17
Medium Priority
?
1,155 Views
Last Modified: 2013-11-16
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
Comment
Question by:dougc11
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 3
17 Comments
 
LVL 7

Expert Comment

by:grzessio
ID: 22715588
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
 

Author Comment

by:dougc11
ID: 22715663
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
 

Author Comment

by:dougc11
ID: 22720342
The error I get is: "ORA-01898: too many precision specifiers"
0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
LVL 7

Expert Comment

by:Dr_Billy
ID: 22722139
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
 
LVL 7

Expert Comment

by:grzessio
ID: 22722213
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
 

Author Comment

by:dougc11
ID: 22722566
Can you walk me through this logic please?
0
 

Author Comment

by:dougc11
ID: 22722803
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
 
LVL 7

Expert Comment

by:grzessio
ID: 22722863
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
 
LVL 7

Expert Comment

by:grzessio
ID: 22722872
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
 
LVL 7

Accepted Solution

by:
grzessio earned 1400 total points
ID: 22722880
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
 

Author Comment

by:dougc11
ID: 22723181
Can you show me the oracle date version of the same range? I increased the point range for your effort.
0
 
LVL 7

Expert Comment

by:grzessio
ID: 22723348
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
 
LVL 7

Expert Comment

by:Dr_Billy
ID: 22730784
Did you try my post ?
0
 

Author Comment

by:dougc11
ID: 22730847
Yes, you both (Dr Billy & grzessio) answered my question correctly , But I'm not sure what to do about the points.
0
 
LVL 7

Expert Comment

by:Dr_Billy
ID: 22731364
In this case you split the points
0
 

Author Closing Comment

by:dougc11
ID: 31506050
thanks very much!
0

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question