Solved

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

Posted on 2008-10-14
17
1,126 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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 350 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help with Oracle IF statment 5 36
Comparison query - 4 columns 9 42
Oracle Errors 11 52
SQL Get Store Procedure Column Name As Row 5 41
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
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…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

733 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