Solved

Oracle Decode using Between criteria in Oracle 8

Posted on 2007-03-28
4
3,711 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 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

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…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

809 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