Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Oracle create function and return ref_cursor 1
- Thread starter davism
- Start date
- Thread starter
- #41
SantaMufasa,
Here are the items I'm working with:
The Table:
The SP:
The function:
I noted the function itself has a 2 BEGINS and 2 ENDS. I'm not entirely sure. The second BEGIN is after the declar and if I take that out I get a failure.
Hope this helps some. Again, they compile but I cannot get it to work with some of the testing using the PL/SQL.
Any information you may have on this would be greatly appreciated.
Thanks!
Here are the items I'm working with:
The Table:
Code:
CREATE GLOBAL TEMPORARY TABLE tst_DATE_LIST
(
DT_TYPE VARCHAR2(25) NULL,
DT_OUT VARCHAR2(23) NULL
)
ON COMMIT DELETE ROWS
ORGANIZATION HEAP
/The SP:
Code:
CREATE OR REPLACE PROCEDURE tstSP_DATE_TEXT
(
-- =============================================
-- declare input and OUTPUT parametes
-- =============================================
pdtInDate IN DATE,
pvcOutDate OUT VARCHAR2
)
AS
BEGIN
DECLARE
lchMonth CHAR(2);
lchDay CHAR(2);
lchYear CHAR(4);
lchHour CHAR(2);
lchMin CHAR(2);
lchSec CHAR(2);
lchMilSec CHAR(3);
-- =============================================
-- Calculate text portion of dates and place in variables
-- =============================================
BEGIN
lchMonth := to_char(pdtInDate,'MM');
lchDay := to_char(pdtInDate,'DD');
lchYear := to_char(pdtInDate,'YYYY');
lchHour := to_char(pdtInDate,'HH24');
lchMin := to_char(pdtInDate,'MI');
lchSec := to_char(pdtInDate,'SS');
lchMilSec := to_char(pdtInDate,'MS');
-- zero fil
IF to_number(lchMonth) < 10 THEN
lchMonth := '0' + lchMonth;
END IF;
-- zero fill
IF to_number(lchDay) < 10 THEN
lchDay := '0' + lchDay;
END IF;
-- zero fill
IF to_number(lchHour) < 10 THEN
lchHour := '0' + lchHour;
END IF;
-- zero fill
IF to_number(lchMin) < 10 THEN
lchMin := '0' + lchMin;
END IF;
--zero fill
IF to_number(lchSec) < 10 THEN
lchSec := '0' + lchSec;
END IF;
-- zero fill
IF to_number(lchMilSec) < 10 THEN
lchMilSec := '00' + lchMilSec;
END IF;
pvcOutDate := lchMonth + '/' + lchDay || '/' + lchYear + ' ' + lchHour + ':' + lchMin + ':' + lchSec + '.' + lchMilSec;
END;
END tstSP_DATE_TEXT;
/The function:
Code:
CREATE OR REPLACE FUNCTION tstSP_RUNDATE_LIST
(
-- =============================================
-- declare input parametes
-- =============================================
pRESULT_CURSOR OUT SYS_REFCURSOR,
pdtStartDate IN DATE := to_date('01/01/1753','mm/dd/yyyy'),
pvcWeekday IN VARCHAR2 default null
)
RETURN NUMBER
AS
BEGIN
DECLARE
ldtEndMonthp DATE;
ldtEndMontht DATE;
ldtEndMonthn DATE;
ldtBegMonth DATE;
lnDOW INT;
lnDOWThis INT;
ldtSpecDay DATE;
lnDOWAbs INT;
lnDOWEnd INT;
ldtBatchDate DATE;
ldtBeforeStart DATE;
lvcTextDate VARCHAR2(23);
ldtDateCalc DATE;
ldtTempDate DATE;
lvcWeekday VARCHAR2(20);
BEGIN
--If the current date has not been input then get the current date
IF (pdtStartDate = to_date('01/01/1753','mm/dd/yyyy')) THEN
ldtTempDate := SYSDATE ;
END IF;
--If the weekday has not bee input then get the weekday of @pdtStartDate
--expressed as text date.
lvcWeekday := pvcWeekday;
IF (lvcWeekday || 'b' = 'b') THEN
lvcWeekday :=
CASE to_char(ldtTempDate, 'dw')
WHEN 1 THEN 'SUNDAY'
WHEN 2 THEN 'MONDAY'
WHEN 3 THEN 'TUESDAY'
WHEN 4 THEN 'WEDNESDAY'
WHEN 5 THEN 'THURSDAY'
WHEN 6 THEN 'FRIDAY'
WHEN 7 THEN 'SATURDAY'
END;
END IF;
lnDOW :=
CASE UPPER(pvcWeekday)
WHEN 'SUNDAY' THEN 1
WHEN 'MONDAY' THEN 2
WHEN 'TUESDAY' THEN 3
WHEN 'WEDNESDAY' THEN 4
WHEN 'THURSDAY' THEN 5
WHEN 'FRIDAY' THEN 6
WHEN 'SATURDAY' THEN 7
WHEN 'SUN' THEN 1
WHEN 'MON' THEN 2
WHEN 'TUES' THEN 3
WHEN 'WED' THEN 4
WHEN 'THUR' THEN 5
WHEN 'FRI' THEN 6
WHEN 'SAT' THEN 7
ELSE 0
END;
IF lnDOW = 0 THEN
RETURN 17001; --@@ERROR ***FIX***
END IF;
--Each date calculation has 3 parts, Calculate the date, Convert it to text, and insert
--it into the temporary table.
--
-- ########## Store today and input dates in temporary table
ldtDateCalc := pdtStartDate;
tstSP_DATE_TEXT (ldtDateCalc, lvcTextDate);
INSERT INTO tst_date_list VALUES ('CURRENT_DATE', lvcTextDate);
tstSP_DATE_TEXT (pdtStartDate, lvcTextDate);
INSERT INTO tst_date_list VALUES ('INPUT_DATE', lvcTextDate);
-- ############ Previous month end caculation inserted into temporary table
ldtEndMonthp := DATEADD('DD', - to_char(ldtTempDate, 'DD'), ldtTempDate);
tstSP_DATE_TEXT (ldtEndMonthp, lvcTextDate);
INSERT INTO tst_date_list VALUES ('LAST_PREV_MONTH', lvcTextDate);
-- ########### This month end date caculation inserted into temporary table
ldtEndMontht := DATEADD('DD', - to_char(DATEADD('MM', 1, ldtTempDate),'DD'), DATEADD('MM', 1, ldtTempDate));
tstSP_DATE_TEXT (ldtEndMontht, lvcTextDate);
INSERT INTO tst_date_list VALUES ('LAST_CUR_MONTH', lvcTextDate);
-- list temporary table send data to OUTPUT #####################################
OPEN pRESULT_CURSOR FOR
SELECT DT_TYPE, DT_OUT from tst_date_list
ORDER BY DT_TYPE;
RETURN 0;
END;
END;
-- Stored procedure ends here
/I noted the function itself has a 2 BEGINS and 2 ENDS. I'm not entirely sure. The second BEGIN is after the declar and if I take that out I get a failure.
Hope this helps some. Again, they compile but I cannot get it to work with some of the testing using the PL/SQL.
Any information you may have on this would be greatly appreciated.
Thanks!
SantaMufasa
Technical User
Davism,
Oracle's date-processing functions are probably the most powerful among all computing environments. As a result, Oracle's TO_CHAR function obsoletes:[ul][li]your entire "tstSP_DATE_TEXT" procedure (including the left-zero-fill code, which is the TO_CHAR default), and [/li][li]your two sections of "CASE" code that appeared in your "tstSP_RUNDATE_LIST" function.[/li][/ul]
Additionally, since I don't believe that the date, '01/01/1753' has any actual business significance to you, I suggest that you make the default value for "pdtStartDate" (as it is for "pvcWeekday"), NULL. Then to test for NULL, instead of contriving a comparison such as
...you should, instead, say:
Other issues:[ul][li]I don't see where you ever actually use the variable "lnDOW"...You set the value, but you never seem to use it anywhere. (Until we resolve its use, I've removed code and references relating to "lnDOW" in my code modification, below.)[/li][li]String concatenation in Oracle SQL occurs with the "||" operator (two successive pipe symbols), not with the "+" operator.[/li][li]'CURRENT_DATE' and 'INPUT_DATE' both derive directly from the value of 'pdtStartDate'. Is that what you really want?[/li][li]You use this code:
...to generate the value of 'ldtEndMontht'. Unfortunately, Oracle SQL has no "DATEADD" function. Can you please explain (in plain English) what you wanted the "DATEADD" function to do? (For the time being, I'll presume that you want the last day of the month previous to "ldtTempDate", at time 23:59:59. If that is not what you wanted, we can adjust the code once you explain what you really want for 'ldtEndMontht'.[/li][li]Same issue for 'LAST_CUR_MONTH'...I'll presume that you want 23:59:59 on the last day of the month in which "ldtTempDate" appears.[/li][li]Oracle DATE expressions are significant only to seconds, not milliseconds. If it is a business requirement to store DATE/TIME information down to millisecond accuracy, then you must change datatypes to Oracle's TIMESTAMP datatype. (In the code, below, I have simply removed millisecond references.)[/li][li]Since my simplified, modified code does all of your date manipulations in place (and there are no longer any references to your DECLAREd variables, except for "ldtTempDate"), I have removed all locally declared variables except for "ldtTempDate".[/li][li]Since you are unconditionally returning a "0" at the end of your function regardless of outcome, the RETURN value ("0") becomes meaningless. Therefore, to tighten your code, I have modified the RETURN argument to be the SYS_REFCURSOR, instead.[/li][li]You mention above:
I have taken the liberty to modify your code to include all of the suggestions that I have listed above:
Let us know if the above simplifications do what you want and if you have questions.
![[santa]](/data/assets/smilies/santa.gif "[santa] [santa]")
Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”
Oracle's date-processing functions are probably the most powerful among all computing environments. As a result, Oracle's TO_CHAR function obsoletes:[ul][li]your entire "tstSP_DATE_TEXT" procedure (including the left-zero-fill code, which is the TO_CHAR default), and [/li][li]your two sections of "CASE" code that appeared in your "tstSP_RUNDATE_LIST" function.[/li][/ul]
Additionally, since I don't believe that the date, '01/01/1753' has any actual business significance to you, I suggest that you make the default value for "pdtStartDate" (as it is for "pvcWeekday"), NULL. Then to test for NULL, instead of contriving a comparison such as
Code:
...IF (lvcWeekday || 'b' = 'b')...
Code:
IF lvcWeekday [B][I]IS NULL[/I][/B]...Other issues:[ul][li]I don't see where you ever actually use the variable "lnDOW"...You set the value, but you never seem to use it anywhere. (Until we resolve its use, I've removed code and references relating to "lnDOW" in my code modification, below.)[/li][li]String concatenation in Oracle SQL occurs with the "||" operator (two successive pipe symbols), not with the "+" operator.[/li][li]'CURRENT_DATE' and 'INPUT_DATE' both derive directly from the value of 'pdtStartDate'. Is that what you really want?[/li][li]You use this code:
Code:
ldtEndMonthp := DATEADD('DD', - to_char(ldtTempDate, 'DD'), ldtTempDate);When you create user-defined PL/SQL code objects (e.g. user-defined stored PROCEDURES, FUNCTIONS, PACKAGES, et cetera), the "IS/AS" syntax takes the place of PL/SQL's anonymous blocks' "DECLARE" header. By using the standard "IS/AS" construct, it eliminates the contrived two BEGINS and two END statements that you had in your code posting, above.[/li][/ul]Davism said:
I have taken the liberty to modify your code to include all of the suggestions that I have listed above:
Code:
CREATE OR REPLACE FUNCTION tstSP_RUNDATE_LIST (pdtStartDate DATE default null)
RETURN sys_refcursor
AS
ldtTempDate DATE;
pRESULT_CURSOR sys_refcursor;
BEGIN
--If the current date has not been input then get the current date
ldtTempDate := pdtStartDate;
IF pdtStartDate IS NULL THEN
ldtTempDate := SYSDATE;
END IF;
-- ########## Store today and input dates in temporary table
INSERT INTO tst_date_list VALUES ('CURRENT_DATE', to_char(pdtStartDate,'MM/DD/YYYY hh24:mi:ss'));
INSERT INTO tst_date_list VALUES ('INPUT_DATE', to_char(pdtStartDate,'MM/DD/YYYY hh24:mi:ss'));
-- ############ Previous month end caculation inserted into temporary table
INSERT INTO tst_date_list VALUES ('LAST_PREV_MONTH'
, to_char(trunc(ldtTempDate,'MM')-(1/24/60/60),'MM/DD/YYYY hh24:mi:ss'));
-- ########### This month end date caculation inserted into temporary table
INSERT INTO tst_date_list VALUES ('LAST_CUR_MONTH'
, to_char(trunc(last_day(ldtTempDate))+1-(1/24/60/60),'mm/dd/yyyy hh24:mi:ss'));
-- list temporary table send data to OUTPUT #####################################
OPEN pRESULT_CURSOR FOR
SELECT DT_TYPE, DT_OUT from tst_date_list
ORDER BY DT_TYPE;
RETURN pResult_cursor;
END;
-- FUNCTION ends here
/
Function created.
set serveroutput on
declare
foo sys_refcursor;
y tst_DATE_LIST%rowtype;
begin
foo := tstSP_RUNDATE_LIST(sysdate);
loop
fetch foo into y;
exit when foo%notfound;
dbms_output.put_line(y.dt_type||', '||y.dt_out);
end loop;
end;
/
CURRENT_DATE, 12/05/2008 20:51:17
INPUT_DATE, 12/05/2008 20:51:17
LAST_CUR_MONTH, 12/31/2008 23:59:59
LAST_PREV_MONTH, 11/30/2008 23:59:59
PL/SQL procedure successfully completed.(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”
- Thread starter
- #43
Oh man, I had that all jacked up then. That's my knowledge of SQL Server SQL trying to get implemented into Oracle.
I actually found some information after I sent that to you as well that I can even see about utilizing.
Oracle does have functions like you mentioned for dates. Ones I never paid much close attention to.
For the last day of the current month and last day of the previous month can you not just use the LAST_DAY function. So you can use LAST_DAY of SYSDATE for the last day of the current month.
Although, you have:
You are using last day in that. Why and what is the:
getting at here? The +1 is for what? Then the -(1/24/60/60) is what?
For the last day of the previous month can't you use the function ADD_MONTHS(SYSDATE,-1) and then get the LAST_DAY of that value so something like LAST_DAY(ADD_MONTHS(SYSDATE,-1))
The DATEADD function was from SQL Server where that is used for DATE calculations. Obviously, I thought since it compiled in Oracle then that was value and those would be able to be used there as well. From what you mentioned, it seems as though that was a false assumption and there was no compile error on Oracle as a result. Why that is I do not know but it's very strange and I would've expected a compilation error as that function just is not there as a system function or a user-defined function. Do you have any idea?
Sorry about the lnDow thing. I was trying to get is prepared for a literal day of the week "SUNDAY", "MONDAY", etc and extended to use a numeric value of the day of the week (1 for "SUNDAY", 2 for "MONDAY", etc). Obviously, I didn't have that complete yet so it's ok that you removed it.
I was looking at one thing as well that you may have some information on as I'm not finding relatively easy way without the DATEADD function. The DATEADD function was relatively simple and straight forward on this. What I would try to do it get the 1st of the current month. In SQL Server it would be a DATEADD to calculate the last day of the previous month. Then I would use a DATEADD(DD,1, {previous last day of last month variable}) to give me the first of the current month.
How is that done in Oracle?
LAST_DAY(ADD_MONTH(SYSDATE,-1)) for the last day of the previous month, then take that value and do a +1. So something like:
LASTDAYPREV = LAST_DAY(ADD_MONTH(SYSDATE,-1));
FIRSTDAYCUR = to_DATE(LASTDAYPREV+1);
Please let me know on that.
With respect to having the times on the dates. I am not completely sure yet. I believe that if I do not want them there I can do a TRUNC(date) like you have whereas the TRUNC removes the time references.
Am I catching on to this better? And thank you so much so far!
I actually found some information after I sent that to you as well that I can even see about utilizing.
Oracle does have functions like you mentioned for dates. Ones I never paid much close attention to.
For the last day of the current month and last day of the previous month can you not just use the LAST_DAY function. So you can use LAST_DAY of SYSDATE for the last day of the current month.
Although, you have:
Code:
INSERT INTO tst_date_list VALUES ('LAST_CUR_MONTH'
, to_char(trunc(last_day(ldtTempDate))+1-(1/24/60/60),'mm/dd/yyyy hh24:mi:ss'));You are using last day in that. Why and what is the:
Code:
+1-(1/24/60/60)getting at here? The +1 is for what? Then the -(1/24/60/60) is what?
For the last day of the previous month can't you use the function ADD_MONTHS(SYSDATE,-1) and then get the LAST_DAY of that value so something like LAST_DAY(ADD_MONTHS(SYSDATE,-1))
The DATEADD function was from SQL Server where that is used for DATE calculations. Obviously, I thought since it compiled in Oracle then that was value and those would be able to be used there as well. From what you mentioned, it seems as though that was a false assumption and there was no compile error on Oracle as a result. Why that is I do not know but it's very strange and I would've expected a compilation error as that function just is not there as a system function or a user-defined function. Do you have any idea?
Sorry about the lnDow thing. I was trying to get is prepared for a literal day of the week "SUNDAY", "MONDAY", etc and extended to use a numeric value of the day of the week (1 for "SUNDAY", 2 for "MONDAY", etc). Obviously, I didn't have that complete yet so it's ok that you removed it.
I was looking at one thing as well that you may have some information on as I'm not finding relatively easy way without the DATEADD function. The DATEADD function was relatively simple and straight forward on this. What I would try to do it get the 1st of the current month. In SQL Server it would be a DATEADD to calculate the last day of the previous month. Then I would use a DATEADD(DD,1, {previous last day of last month variable}) to give me the first of the current month.
How is that done in Oracle?
LAST_DAY(ADD_MONTH(SYSDATE,-1)) for the last day of the previous month, then take that value and do a +1. So something like:
LASTDAYPREV = LAST_DAY(ADD_MONTH(SYSDATE,-1));
FIRSTDAYCUR = to_DATE(LASTDAYPREV+1);
Please let me know on that.
With respect to having the times on the dates. I am not completely sure yet. I believe that if I do not want them there I can do a TRUNC(date) like you have whereas the TRUNC removes the time references.
Am I catching on to this better? And thank you so much so far!
SantaMufasa
Technical User
Davism,
To assist in answering your questions, I'll post the following query:
I had to presume that you will eventually be using "LAST_CUR_MONTH" and "LAST_PREV_MONTH" as comparison values in queries. If you simply use LAST_DAY(SYSDATE) (without adjusting to the end of the day at 23:59:59), then any row which have date values on the LAST_DAY of the month, but which time component is later than LAST_DAY(SYSDATE) will inappropriately not appear in the result set.
As an example of the above problem, let's look at the following code, which has a DATE value on the LAST_DAY of the month, but the TIME has not been adjusted to extend until one second before midnight (at the end of LAST_DAY):
[/li][li]trunc(last_day(sysdate)) -- yields the last day of the month for sysdate, at midnight on the morning of LAST_DAY.
[/li][li]trunc(last_day(sysdate))+1 -- yields the first day of NEXT month.
[/li][li]trunc(last_day(sysdate))+1-(1/24/60/60) -- Since "1" = one day, then "1/24" = one hour, "1/24/60" = one minute, and "1/24/60/60" = one second. By subtracting (1/24/60/60) from midnight, it yields a time of "23:59:59". This entire complex operation yields "23:59:59" on the LAST_DAY of the month.[/li][/ul]
If, instead of TRUNC, you use ROUND, as you would expect, if the DATE/TIME value is beyond halfway, Oracle moves the value upward to the next increment, rather than unconditionally moving downward in the case of TRUNC.
BTW, Oracle DATE expressions always store the following information:[ul][li]+/- : "-" = Before Christ (B.C.); "+" = Ano Domini (A.D.).
[/li][li]Two digit Century -- Valid ranges: -99 to -0l and +01 - +99.
[/li][li]Two digit Year -- Valid range: 00-99.
[/li][li]Two digit Month -- Valid range: 1-12.
[/li][li]Two digit Day -- Valid range : 1-28,30,31 (depending upon allowable days in month).
[/li][li]Two digit Hour -- Valid range: 0-23.
[/li][li]Two digit Minute -- Valid range: 0-59.
[/li][li]Two digit Second -- Valid range: 0-59.[/li][/ul]I hope the above information is useful. Let me know if you have additional questions.
Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”
To assist in answering your questions, I'll post the following query:
Code:
col a heading "Current Time" format a20
col b heading "Last Day of|Current Month" format a20
col c heading "Midnight (morning)|Last Day of|Current Month" format a20
col d heading "First Day of|Next Month" format a20
col e heading "Last Second of|Current Month" format a20
select to_char(sysdate,'mm/dd/yyyy hh24:mi:ss') a
,to_char(last_day(sysdate),'mm/dd/yyyy hh24:mi:ss') b
,to_char(trunc(last_day(sysdate)),'mm/dd/yyyy hh24:mi:ss') c
,to_char(trunc(last_day(sysdate))+1,'mm/dd/yyyy hh24:mi:ss') d
,to_char(trunc(last_day(sysdate))+1-(1/24/60/60),'mm/dd/yyyy hh24:mi:ss') e
from dual;
Midnight (morning)
Last Day of Last Day of First Day of Last Second of
Current Time Current Month Current Month Next Month Current Month
-------------------- -------------------- -------------------- -------------------- --------------------
12/07/2008 17:54:29 12/31/2008 17:54:29 12/31/2008 00:00:00 01/01/2009 00:00:00 12/31/2008 23:59:59Yes, kind of, depending upon your use/need for the LAST_DAY of the month. That's why I tried to clarify in my reply, above, what your use/need is for LAST_DAY...I said,Davism said:
Notice in my code, above, that LAST_DAY(SYSDATE) does yield the last day of the current month, but it also yields on that last day, a TIME component that is based on the current time, not midnight on the morning of LAST_DAY or just before midnight at the end of LAST_DAY.Mufasa said:
I had to presume that you will eventually be using "LAST_CUR_MONTH" and "LAST_PREV_MONTH" as comparison values in queries. If you simply use LAST_DAY(SYSDATE) (without adjusting to the end of the day at 23:59:59), then any row which have date values on the LAST_DAY of the month, but which time component is later than LAST_DAY(SYSDATE) will inappropriately not appear in the result set.
As an example of the above problem, let's look at the following code, which has a DATE value on the LAST_DAY of the month, but the TIME has not been adjusted to extend until one second before midnight (at the end of LAST_DAY):
Code:
set serveroutput on format wrap
declare
target_time date := to_date('31-DEC-2008 21:00:00','dd-MON-YYYY hh24:mi:ss');
now date := sysdate;
last_day_only date := last_day(sysdate);
last_day_last_second date := trunc(last_day(sysdate))+1-(1/24/60/60);
is_or_isnot varchar2(30);
begin
if target_time between now and last_day_only then
is_or_isnot := ' is between ';
else
is_or_isnot := ' is not between ';
end if;
dbms_output.put_line(
to_char(target_time,'mm/dd/yyyy hh24:mi:ss')||is_or_isnot
||to_char(now,'mm/dd/yyyy hh24:mi:ss')||' and '
||to_char(last_day_only,'mm/dd/yyyy hh24:mi:ss')||'.'
);
if target_time between now and last_day_last_second then
is_or_isnot := ' is between ';
else
is_or_isnot := ' is not between ';
end if;
dbms_output.put_line(
to_char(target_time,'mm/dd/yyyy hh24:mi:ss')||is_or_isnot||
to_char(now,'mm/dd/yyyy hh24:mi:ss')||' and '||
to_char(last_day_last_second,'mm/dd/yyyy hh24:mi:ss')||'.');
end;
/
12/31/2008 21:00:00 is not between 12/07/2008 17:55:41 and 12/31/2008 17:55:41.
12/31/2008 21:00:00 is between 12/07/2008 17:55:41 and 12/31/2008 23:59:59.I coded these calculations in order to yield dates that are one second before midnight. Referring to my first code example of this reply:[ul][li]last_day(sysdate) -- yields the last day of the month for sysdate, but the TIME matches SYSDATE.Davism said:
[/li][li]trunc(last_day(sysdate)) -- yields the last day of the month for sysdate, at midnight on the morning of LAST_DAY.
[/li][li]trunc(last_day(sysdate))+1 -- yields the first day of NEXT month.
[/li][li]trunc(last_day(sysdate))+1-(1/24/60/60) -- Since "1" = one day, then "1/24" = one hour, "1/24/60" = one minute, and "1/24/60/60" = one second. By subtracting (1/24/60/60) from midnight, it yields a time of "23:59:59". This entire complex operation yields "23:59:59" on the LAST_DAY of the month.[/li][/ul]
Yes, you can do that, but you will still need to adjust the TIME component to 23:59:59.Davism said:
All I know is that Oracle does throw an error indiating that it does not recognize "dateadd":Davism said:
Code:
select dateadd(sysdate) from dual;
*
ERROR at line 1:
ORA-00904: "DATEADD": invalid identifierWe either ROUND or TRUNC (truncate) time values, while specifying the ROUND/TRUNC magnitude (i.e., minute, hour, day, week, month, year, century):Davism said:
Code:
col a heading "Beginning of Minute" format a20
col b heading "Beginning of Hour" format a20
col c heading "Beginning of Day" format a20
col d heading "Beginning of Week" format a20
col e heading "Beginning of Month" format a20
col f heading "Beginning of Year" format a20
col g heading "Beginning of Century" format a20
select to_char(trunc(sysdate,'MI'),'mm/dd/yyyy hh24:mi:ss') a -- trunc to beg of minute
,to_char(round(sysdate,'HH'),'mm/dd/yyyy hh24:mi:ss') b -- trunc to beg of hour
,to_char(trunc(sysdate),'mm/dd/yyyy hh24:mi:ss') c -- trunc to beg of day
,to_char(trunc(sysdate,'W'),'mm/dd/yyyy hh24:mi:ss') d -- trunc to beg of week
,to_char(trunc(sysdate,'MM'),'mm/dd/yyyy hh24:mi:ss') e -- trunc to beg of month
,to_char(trunc(sysdate,'Y'),'mm/dd/yyyy hh24:mi:ss') f -- trunc to beg of year
,to_char(trunc(sysdate,'CC'),'mm/dd/yyyy hh24:mi:ss') g -- trunc to beg of Century
from dual;
Beginning of Minute Beginning of Hour Beginning of Day Beginning of Week
-------------------- -------------------- -------------------- --------------------
12/07/2008 17:56:00 12/07/2008 18:00:00 12/07/2008 00:00:00 12/01/2008 00:00:00
Beginning of Month Beginning of Year Beginning of Century
-------------------- -------------------- --------------------
12/01/2008 00:00:00 01/01/2008 00:00:00 01/01/2001 00:00:00As you can see from the above code, TRUNC does not remove time references...it just alters the value to a specific boundary. Oracle DATE expressions always have a time component. Ignoring that feature can lead to significant logic errors.Davism said:
BTW, Oracle DATE expressions always store the following information:[ul][li]+/- : "-" = Before Christ (B.C.); "+" = Ano Domini (A.D.).
[/li][li]Two digit Century -- Valid ranges: -99 to -0l and +01 - +99.
[/li][li]Two digit Year -- Valid range: 00-99.
[/li][li]Two digit Month -- Valid range: 1-12.
[/li][li]Two digit Day -- Valid range : 1-28,30,31 (depending upon allowable days in month).
[/li][li]Two digit Hour -- Valid range: 0-23.
[/li][li]Two digit Minute -- Valid range: 0-59.
[/li][li]Two digit Second -- Valid range: 0-59.[/li][/ul]I hope the above information is useful. Let me know if you have additional questions.
Mufasa(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”
SantaMufasa
Technical User
As a clarification, In my result set, above, the beginning of the hour should have read "17:00:00", but instead reads "18:00:00" because (as you will notice in the code) I used the function, "ROUND" instead of "TRUNC".
Also, somehow my chubby fingers hit the wrong key, above. The last set of bullets should have read:
[ul][li]-99 to -01 (not -99 to -0l)[/li][/ul]Sorry for any confusion caused by my mistakes.
Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”
Also, somehow my chubby fingers hit the wrong key, above. The last set of bullets should have read:
[ul][li]-99 to -01 (not -99 to -0l)[/li][/ul]Sorry for any confusion caused by my mistakes.
Mufasa(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”
- Thread starter
- #46
SantaMufasa,
Thanks for the information and VERY informative!
One quick question...sometimes the time is applicable and warranted. Other times it will not be. How do I get the SYSDATE or any other date WITHOUT any time? What is the best means for that. I thought TRUNC was it but if you're saying that TRUNC doesn't exclude the time it just gives the 00:00.00 (midnight) then that may not work entirely.
So, in your example of 12/31/2008 21:00:00 not being between 12/07/2008 17:55:41 and 12/31/2008 17:55:41 that makes sense but if it is to go off of date boundaries then 12/31/2008 IS BETWEEN 12/07/2008 and 12/31/2008. Assuming that BETWEEN is inclusive of the values it is between which it more often than not is.
How do you get JUST the date without the time?
Thanks for the information and VERY informative!
One quick question...sometimes the time is applicable and warranted. Other times it will not be. How do I get the SYSDATE or any other date WITHOUT any time? What is the best means for that. I thought TRUNC was it but if you're saying that TRUNC doesn't exclude the time it just gives the 00:00.00 (midnight) then that may not work entirely.
So, in your example of 12/31/2008 21:00:00 not being between 12/07/2008 17:55:41 and 12/31/2008 17:55:41 that makes sense but if it is to go off of date boundaries then 12/31/2008 IS BETWEEN 12/07/2008 and 12/31/2008. Assuming that BETWEEN is inclusive of the values it is between which it more often than not is.
How do you get JUST the date without the time?
SantaMufasa
Technical User
To look only at the DATE portion, I would have modified my code, above, to read:
This issue is precisely why I said earlier:
Let us know,
Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”
Code:
...
if trunc(target_time) between trunc(now)
and trunc(last_day_only) then
...Does this properly resolve your question(s)?Mufasa said:
Let us know,
Mufasa(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”
- Thread starter
- #48
When I try to specify a date for the use with the pdtStateDate. I get a format date error. I'm trying to pass like 12/02/2008.
Am I missing something there? Since Oracle dates have to have the time the to_date may not be applicable. Rather a date is and if the date isn't provided then it should go to that IN_DATE value on the header.
Am I missing something obvious there?
Am I missing something there? Since Oracle dates have to have the time the to_date may not be applicable. Rather a date is and if the date isn't provided then it should go to that IN_DATE value on the header.
Am I missing something obvious there?
SantaMufasa
Technical User
If you are trying to pass a literal "12/02/2008" as a DATE, then you should say:
Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”
Code:
...to_date('12/02/2008','mm/dd/yyyy')...(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”
SantaMufasa
Technical User
Davism,
Haven't heard from you in a few days...How did things turn out? Did you resolve your needs?
Let us know,
Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”
Haven't heard from you in a few days...How did things turn out? Did you resolve your needs?
Let us know,
Mufasa(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”
- Thread starter
- #51
SantaMufasa,
Sorry, I got really caught up in this thing. But the good news is that I got everything taken care of and it's all working.
I was a little stumped in that my global temporary table I had "ON COMMIT DELETE ROWS" which is transaction based and not session based when in the VB program, nothing was ever going into my cursor. I had needed to change it to ON COMMIT PRESERVE ROWS because the workings of ODP does an implied COMMIT which deleted everything. I needed the persistence of a session which is what the PRESERVE ROW's gave me.
I thank you very, very much are your assistance with this. You brought a lot of good things to my attention!
Thanks again!
Sorry, I got really caught up in this thing. But the good news is that I got everything taken care of and it's all working.
I was a little stumped in that my global temporary table I had "ON COMMIT DELETE ROWS" which is transaction based and not session based when in the VB program, nothing was ever going into my cursor. I had needed to change it to ON COMMIT PRESERVE ROWS because the workings of ODP does an implied COMMIT which deleted everything. I needed the persistence of a session which is what the PRESERVE ROW's gave me.
I thank you very, very much are your assistance with this. You brought a lot of good things to my attention!
Thanks again!
- Status
Similar threads
- Locked
- Question