Home » SQL & PL/SQL » SQL & PL/SQL » Issue with time stamp
Issue with time stamp [message #388805] Thu, 26 February 2009 04:54 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

I have a issue with timestamp. When i am executing a function having below query in it. Its errored out.



Query
----
Executing the query inside the function. When I executed the query in sqlplus with same parameters its able to work fine.

SELECT     SR.sr_id, SR.case_id, SR.contact_id, SR.created, SUM(UR.duration) AS duration, CA.custref
FROM        ServiceRequest_ec SR INNER JOIN
                      contacts_ec CO  ON SR.contact_id = CO.contact_id INNER JOIN
                      userresponses_ec your  ON SR.sr_id = UR.sr_id INNER JOIN
                      cases_ec CA  ON SR.case_id = CA.case_id LEFT OUTER JOIN
                      users_ec U  ON CO.user_id = U.user_id
WHERE     U.user_id = 8469 AND
sr.created  >= to_timestamp('28-NOV-08','DD-MON-YY HH24:MI:SS') AND
sr.created  <= to_timestamp('27-FEB-09','DD-MON-YY HH24:MI:SS') 
GROUP BY SR.sr_id, SR.created, CA.custref, SR.contact_id, SR.case_id
ORDER BY SR.sr_id DESC;



Execution
---------


exec :x:=esp_getmyservicerequests_v4(8469,'28-NOV-08','27-FEB-09');


Error
______


SQL> exec :x:=esp_getmyservicerequests_v4(8469,'28-NOV-08','27-FEB-09');
BEGIN :x:=esp_getmyservicerequests_v4(8469,'28-NOV-08','27-FEB-09'); END;

*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at "TEMP.ESP_GETMYSERVICEREQUESTS_V4", line 11
ORA-06512: at line 1




Any help really appreciated

Thanks in advance
Re: Issue with time stamp [message #388807 is a reply to message #388805] Thu, 26 February 2009 04:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It seems the message is self-explantory, does not '28-NOV-08' smaller than 'DD-MON-YY HH24:MI:SS'?

Regards
Michel
Re: Issue with time stamp [message #388809 is a reply to message #388805] Thu, 26 February 2009 05:01 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
@vikram1780,

Check this link.

http://www.techonthenet.com/oracle/errors/ora01830.php

Regards,
Hammer
Re: Issue with time stamp [message #388811 is a reply to message #388807] Thu, 26 February 2009 05:10 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

Thanks for your reply.

If thats the case how it worked in query. I have the same query in function dates as two parameters.

I have tried with complete date picture format as below



SQL> exec :x:=esp_getmyservicerequests_v4(8469,'28-NOV-08 00:00:00','27-FEB-09 0
0:00:00');
BEGIN :x:=esp_getmyservicerequests_v4(8469,'28-NOV-08 00:00:00','27-FEB-09 00:00
:00'); END;

*
ERROR at line 1:
ORA-01849: hour must be between 1 and 12
ORA-06512: at line 1





Thanks in advance
Re: Issue with time stamp [message #388818 is a reply to message #388811] Thu, 26 February 2009 05:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As we don't know how the function is defined, we can't say if there is any parameter mismatch.

Regards
Michel
Re: Issue with time stamp [message #388825 is a reply to message #388818] Thu, 26 February 2009 05:42 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

Here below is my function.


create or replace FUNCTION "ESP_GETMYSERVICEREQUESTS_V4"
(
	v_userID NUMBER,
	v_startdate TIMESTAMP,
	v_enddate TIMESTAMP
)return sys_refcursor
AS
v_temp   SYS_REFCURSOR;

BEGIN
OPEN v_temp for SELECT     SR.sr_id, SR.case_id, SR.contact_id, SR.created, SUM(UR.duration) AS duration, CA.custref
FROM        ServiceRequest_ec SR INNER JOIN
                      contacts_ec CO  ON SR.contact_id = CO.contact_id INNER JOIN
                      userresponses_ec your  ON SR.sr_id = UR.sr_id INNER JOIN
                      cases_ec CA  ON SR.case_id = CA.case_id LEFT OUTER JOIN
                      users_ec U  ON CO.user_id = U.user_id
WHERE     U.user_id = v_userID AND
sr.created  >= to_timestamp(v_startdate,'DD-MON-YY HH24:MI:SS') AND
sr.created  <= to_timestamp(v_enddate,'DD-MON-YY HH24:MI:SS') 
GROUP BY SR.sr_id, SR.created, CA.custref, SR.contact_id, SR.case_id
ORDER BY SR.sr_id DESC;
return v_temp;
END;



Any help really appreciated

Thanks in advance
Re: Issue with time stamp [message #388831 is a reply to message #388805] Thu, 26 February 2009 06:00 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
So basically you're passing a string (anything in quotes is a string) to a function that's expecting a timestamp.
Then in that function your converting two timestamps (that's what your parameters are declared as) to timestamps.

That's a lot of implicit data conversions.
You're lucky it ever worked.

It'll probably work a lot better if you convert the initial string value to a timestamp before oracle does it for you rather than after.

EDIT: typo

[Updated on: Thu, 26 February 2009 06:02]

Report message to a moderator

Re: Issue with time stamp [message #388851 is a reply to message #388831] Thu, 26 February 2009 06:35 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member

Hi,

Thanks for your reply.

Could you pls help me with small example.

Thanks in advance
Re: Issue with time stamp [message #388855 is a reply to message #388805] Thu, 26 February 2009 06:47 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
'28-NOV-08 00:00:00'

That's a string (it's in quotes), so you need to convert it to a timestamp.

v_startdate TIMESTAMP,
v_enddate TIMESTAMP

Those are timestamps, so you DON'T need to convert them to timestamps.
Re: Issue with time stamp [message #388859 is a reply to message #388855] Thu, 26 February 2009 07:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Those are timestamps, so you DON'T need to convert them to timestamps.

But you need to convert the values AT or BEFORE the function call.

Regards
Michel
Re: Issue with time stamp [message #388918 is a reply to message #388859] Thu, 26 February 2009 11:40 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member


Hi,

This function is executed from front end asp.net application. It passes parameters as a string.

So i tried to convert it in back end function.

Could anyone let me know how to rewrite the same function in a efficient way.

Thanks in advance

Re: Issue with time stamp [message #388920 is a reply to message #388805] Thu, 26 February 2009 11:46 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
What, you can't do this in asp?


exec :x:=esp_getmyservicerequests_v4(8469,
to_timestamp('28-NOV-08', 'DD-MON-YY HH24:MI:SS') ,
to_timestamp('27-FEB-09', 'DD-MON-YY HH24:MI:SS'));
Re: Issue with time stamp [message #388926 is a reply to message #388920] Thu, 26 February 2009 12:07 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member


Hi,

I have done the same thing inside oracle function where the particular query is executed.

Here below is the procedure.


create or replace FUNCTION "ESP_GETMYSERVICEREQUESTS_V4"
(
	v_userID NUMBER,
	v_startdate TIMESTAMP,
	v_enddate TIMESTAMP
)return sys_refcursor
AS
v_temp   SYS_REFCURSOR;

BEGIN
OPEN v_temp for SELECT     SR.sr_id, SR.case_id, SR.contact_id, SR.created, SUM(UR.duration) AS duration, CA.custref
FROM        ServiceRequest_ec SR INNER JOIN
                      contacts_ec CO  ON SR.contact_id = CO.contact_id INNER JOIN
                      userresponses_ec your  ON SR.sr_id = UR.sr_id INNER JOIN
                      cases_ec CA  ON SR.case_id = CA.case_id LEFT OUTER JOIN
                      users_ec U  ON CO.user_id = U.user_id
WHERE     U.user_id = v_userID AND
sr.created  >= [COLOR=darkred]to_timestamp(v_startdate,'DD-MON-YY HH24:MI:SS') [/COLOR]AND
sr.created  <= [COLOR=darkred]to_timestamp(v_enddate,'DD-MON-YY HH24:MI:SS')[/COLOR] 
GROUP BY SR.sr_id, SR.created, CA.custref, SR.contact_id, SR.case_id
ORDER BY SR.sr_id DESC;
return v_temp;
END;


Re: Issue with time stamp [message #388929 is a reply to message #388805] Thu, 26 February 2009 12:11 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>I have done the same thing inside oracle function where the particular query is executed.
This proves nothing.
It "works" ins spite of being flawed.
Re: Issue with time stamp [message #388932 is a reply to message #388929] Thu, 26 February 2009 12:21 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,


I tried but encountered with below error.

The Bold part in code is the place where i used the to_timestamp function as mentioned above.




create or replace FUNCTION "ESP_GETMYSERVICEREQUESTS_V4"
(
	v_userID NUMBER,
	v_startdate TIMESTAMP,
	v_enddate TIMESTAMP
)return sys_refcursor
AS
v_temp   SYS_REFCURSOR;

BEGIN
OPEN v_temp for SELECT     SR.sr_id, SR.case_id, SR.contact_id, SR.created, SUM(UR.duration) AS duration, CA.custref
FROM        ServiceRequest_ec SR INNER JOIN
                      contacts_ec CO  ON SR.contact_id = CO.contact_id INNER JOIN
                      userresponses_ec your  ON SR.sr_id = UR.sr_id INNER JOIN
                      cases_ec CA  ON SR.case_id = CA.case_id LEFT OUTER JOIN
                      users_ec U  ON CO.user_id = U.user_id
WHERE     U.user_id = v_userID AND
sr.created  >= [B]to_timestamp(v_startdate,'DD-MON-YY HH24:MI:SS')[/B] AND
sr.created  <= [B]to_timestamp(v_enddate,'DD-MON-YY HH24:MI:SS') [/B]
GROUP BY SR.sr_id, SR.created, CA.custref, SR.contact_id, SR.case_id
ORDER BY SR.sr_id DESC;
return v_temp;
END;




SQL> exec :x:=esp_getmyservicerequests_v4(8469,'28-NOV-08','27-FEB-09');
BEGIN :x:=esp_getmyservicerequests_v4(8469,'28-NOV-08','27-FEB-09'); END;

*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at "TEMP.ESP_GETMYSERVICEREQUESTS_V4", line 11
ORA-06512: at line 1






Thanks in advance

[Updated on: Thu, 26 February 2009 12:26]

Report message to a moderator

Re: Issue with time stamp [message #388933 is a reply to message #388932] Thu, 26 February 2009 12:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't understand that your parameters ARE timestamps.
You HAVE TO convert your input values to timestamp.
you HAVE TO remove to_timestamp from your code.

The ONLY alternative (and it will work) is to change your parameter type to string.

Regards
Michel
Re: Issue with time stamp [message #389037 is a reply to message #388918] Fri, 27 February 2009 01:43 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
vikram1780 wrote on Thu, 26 February 2009 18:40


Hi,

This function is executed from front end asp.net application. It passes parameters as a string.

So i tried to convert it in back end function.

Could anyone let me know how to rewrite the same function in a efficient way.

Thanks in advance



If .Net can't send Timestamps (very unlikely), then you must change your input parameters to something .Net CAN send.
Previous Topic: rollback procedure
Next Topic: xor check constraint
Goto Forum:
  


Current Time: Mon Dec 05 20:54:56 CST 2016

Total time taken to generate the page: 0.12299 seconds