Solved

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

Posted on 2008-10-14
17
1,079 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
  • 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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup

762 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now