Date range checking from DOS

[bengalliboy]

I have got two dates in a table and I need to comapre if today's (system date) falls between the two date field. I am thinking to use sqlplus.... What would be the best way to send a quary to ORACLE and come back with a return code in DOS. What I want some thing like this:

CHECK DAte:
If sysdate between dt_field1 and dt_field2
then 'good' >>'0'
else die >> 9
end

Now I want to capture return code '0' or '9' and echo a message.
TIA

 

[SantaMufasa]

Bengalli,

The following should work fine for you:

create or replace function check_date
	(date_to_check date
	,earlier_date date
	,later_date date)
	return varchar2 is
begin
	if date_to_check is null then
		return 'Error: "date_to_check" must not be NULL.';
	elsif earlier_date is null then
		return 'Error: "earlier_date" must not be NULL.';
	elsif later_date is null then
		return 'Error: "later_date" must not be NULL.';
	end if;
	if date_to_check between earlier_date and later_date then
		return '0 - '||date_to_check||' between '||
			earlier_date||' and '||later_date;
	else
		return '9 - '||date_to_check||' is not between '||
			earlier_date||' and '||later_date;
	end if;
end;
/

Function created.

col a heading "Check Dates" format a50
select check_date(sysdate,sysdate-1,sysdate+1)a from dual;

Check Dates
---------------------------------------------
0 - 07-SEP-04 between 06-SEP-04 and 08-SEP-04

You can, of course, replace (in the above sample execution) the "dual" table with your table name, "sysdate-1" with "dt_field1", and "sysdate+1" with "dt_field2". You can change the return messages to fit your specific preferences, as well.

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 15:33 (07Sep04) UTC (aka "GMT" and "Zulu"), 08:33 (07Sep04) Mountain Time)

 

[bengalliboy]

Hey Thanks a lot! My problem is, I can not create any procedure in that db. So I have got to check this with plain SQL. Can I use Case statement? Also how do I get the status to DOS?
Many Thanks!!!

 

[SantaMufasa]

Sure, here is straight SQL code to make that happen:

select case when sysdate between sysdate -1 and sysdate +1 then
   '0 - some message' else
   '9 - some other message' end date_check
from dual;

DATE_CHECK
----------------
0 - some message

1 row selected.

...and, if you don't mind my asking, why is it that you need to pass the result out to DOS? Your answer could affect the suggestion for getting the result to DOS.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:18 (07Sep04) UTC (aka "GMT" and "Zulu"), 09:18 (07Sep04) Mountain Time)

 

[bengalliboy]

Hi,
Thanks again, One reason I need to send the result out to DOS, because I am performing several other steps, like manipulating a dos file etc based on this SQL result.

So I send the SQL to ORACLE and if the todays date fall into the date between the date stored on my ORACLE table then I remove a file in DOS and move it to some where else and load it to some thing else etc. So you can see different activities is involved.

How can I do the following in ORACLE:
SELECT
CASE
WHEN SYSDATE BETWEEN DATE1 AND DATE2
THEN '0'
ELSE '9'
END

AND return the result to DOS>?
TIA