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

x
?
Solved

Oracle Decode using Between criteria in Oracle 8

Posted on 2007-03-28
4
Medium Priority
?
3,729 Views
Last Modified: 2013-12-19
I want to use Oracle decode command using a between syntax.
I have Oracle 8.0.5.
I want to return from a date file the day, on condition that if the time is between 8 and 23 it would return the same day, but if the time is between 0 and 7 it woud return the previous day.
Its a working shift.
I used:
select decode(to_char(weight_out_date,'HH'),(between 8 and 11),to_char(weight_out_date,'DD-MM-YYYY'),
to_char(weight_out_date-1,'DD-MM-YYYY'))) Date_out

Pls advice accordingly
0
Comment
Question by:Ehab Salem
[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
  • 2
4 Comments
 
LVL 48

Accepted Solution

by:
schwertner earned 1500 total points
ID: 18806680
decode(to_char(weight_out_date,'HH'), 8 ,to_char(weight_out_date,'DD-MM-YYYY'),
9 ,to_char(weight_out_date,'DD-MM-YYYY'),
10 ,to_char(weight_out_date,'DD-MM-YYYY'),
11,to_char(weight_out_date,'DD-MM-YYYY'),
to_char(weight_out_date-1,'DD-MM-YYYY'))

CASE is also possibility, but it is not introduced in Oracle 8
0
 
LVL 27

Expert Comment

by:sujith80
ID: 18806797
select decode(sign(to_number(to_char(weight_out_date,'HH24'))-8), -1, to_char(weight_out_date-1,'DD-MM-YYYY') , to_char(weight_out_date,'DD-MM-YYYY') )
from <table>;
0
 
LVL 14

Author Comment

by:Ehab Salem
ID: 18807145
Well, I think sujith80 solution will not work. I need from 8 AM to 12 AM to be the same day, and from 12 AM to 8 AM to be the previous day.
With some additions to schwertner solution it worked, I just added 12,13,... till 23, and corrected:
decode(to_char(weight_out_date,'HH') to be decode(to_char(weight_out_date,'HH24'),

Thanks
0
 
LVL 27

Expert Comment

by:sujith80
ID: 18807176
good,
but have a look at the question posted "if the time is between 8 and 23 it would return the same day, but if the time is between 0 and 7 it woud return the previous day."

If you like to change it as "from 8 AM to 12 AM to be the same day, and from 12 AM to 8 AM to be the previous day" - it can be done with a minor adjustment in the above posted query.
0

Featured Post

Technology Partners: 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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

610 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