Solved

Oracle Decode using Between criteria in Oracle 8

Posted on 2007-03-28
4
3,706 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
  • 2
4 Comments
 
LVL 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

911 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

22 Experts available now in Live!

Get 1:1 Help Now