Solved

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

Posted on 2008-10-14
17
1,138 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

691 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