Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2008-10-14
17
Medium Priority
?
1,170 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

What we learned in Webroot's webinar on multi-vector protection.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

604 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