Solved

Oracle Decode using Between criteria in Oracle 8

Posted on 2007-03-28
4
3,715 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 500 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying 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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

749 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