Solved

Oracle Decode using Between criteria in Oracle 8

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Difference in number of minutes between 2 timestamps 16 49
history tablespace temp usage 2 38
Processing of multiple cursor 6 45
Help on model clause 5 29
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

786 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