OraFAQ Forum: SQL & PL/SQL » Date format in PL/SQL vs. PSP

Home » SQL & PL/SQL » SQL & PL/SQL » Date format in PL/SQL vs. PSP

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Date format in PL/SQL vs. PSP [message #613379]

Tue, 06 May 2014 02:26

agarfin
Messages: 3
Registered: May 2014

Junior Member

Im having a strange issue I cant seem to fix converting a stored procedure to a pl/sql server page (psp). I made a test procedure and psp page to simplify the code.

CREATE OR REPLACE PROCEDURE test(
    Start_date_Text IN VARCHAR2,
    Start_time_text IN VARCHAR2)
AS
  test2 DATE;
BEGIN
  test2:= to_date(Start_date_Text || ' ' || Start_time_text, 'YYYY-MM-DD HH24:MI');
  dbms_output.put_line(test2);
END;

Executing this procedure with execute test('2014-03-04' , '17:00'); gives me the output 04-03-14 17:00.

My psp page looks like this:

<%@page language="PL/SQL"%>
<%@plsql procedure="testPage"%>

<%@plsql parameter="smartplugID_text" default="null"%>
<%@plsql parameter="Start_date_Text" default="null"%>
<%@plsql parameter="End_date_Text" default="null"%>
<%@plsql parameter="Start_time_text" default="null"%>
<%@plsql parameter="end_time_text" default="null"%>

<%@plsql parameter="FormsButton1" default="null"%>
 
<%! 
 test2 DATE;
%> 
<%
  test2:= to_date(Start_date_Text || ' ' || Start_time_text, 'YYYY-MM-DD HH24:MI');
%> 
<html>
<head>
<title>Test Page</title>
</head>
<body>
<div align="center"><p><h1>Test Date Conversion Page</h1></p><hr>
</div>
<p>
Start_date_Text: <%=Start_date_Text%> <br>
Start_time_text: <%=Start_time_text%> <br><br><br>
Converted Date: <%=test2%> <br>
</P>
</body>
</html>

When loaded in a browser test2 = 05-MAY-14. For some reason the time portion is being dropped
and I cant figure out why. Any help or suggestions would be greatly appreciated. The input values
for Start_date_Text and Start_time_text are coming from another psp page using the HTML5
<input type="date" name="Start_date_Text"> and <input type="time" name="Start_time_text">.

Thanks!

Report message to a moderator

Re: Date format in PL/SQL vs. PSP [message #613381 is a reply to message #613379]

Tue, 06 May 2014 03:07

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

When you display a DATE variable, if you don't use TO_CHAR you use a default format (which is by default DD-MON-RR).
So different contexts may mean different default formats.
And so test2 should be of type VARCHAR2 (you display a string) and your assignment:
test2:= to_char(to_date(Start_date_Text || ' ' || Start_time_text, 'YYYY-MM-DD HH24:MI'), ''YYYY-MM-DD HH24:MI')
or simply (if you are sure about your data:
test2:= Start_date_Text || ' ' || Start_time_text;

Report message to a moderator

Re: Date format in PL/SQL vs. PSP [message #613382 is a reply to message #613381]

Tue, 06 May 2014 03:33

agarfin
Messages: 3
Registered: May 2014

Junior Member

Thanks for the reply! That does seem to fix the output. If both the psp page and pl/sql I used were running on the same server shouldn't the default format have been the same? For simplicity I only posted the test page I was using and not my full psp page. In hindsight maybe this was a mistake. Im trying to convert the two independent strings Start_date_text and start_date_time into one date value so I can use pl/sqls ability to add/subtract dates. I am using the start and end date values for various calculations and need to be able to increment them by a day in a while loop. Is there an easy way to do this with varchar2 date strings? Also is it possible to globally define the default date format?

Again thanks for your time.

Report message to a moderator

Re: Date format in PL/SQL vs. PSP [message #613383 is a reply to message #613382]

Tue, 06 May 2014 03:38

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

shouldn't the default format have been the same?

Default first depends on client and if client does not set one on server.

Quote:

Is there an easy way to do this with varchar2 date strings?

No, DATE is then the type to use and DATE has no format (format stuff appears when you want to display).

Quote:

is it possible to globally define the default date format?

No, as I said, default format can be defined at each (Oracle) client.
Using an application server you can set it on it but it is NEVER a good idea to rely on default format an implicit conversion.

Report message to a moderator

Re: Date format in PL/SQL vs. PSP [message #613384 is a reply to message #613382]

Tue, 06 May 2014 03:53

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

agarfin wrote on Tue, 06 May 2014 14:03

Is there an easy way to do this with varchar2 date strings? Also is it possible to globally define the default date format?

Read this excellent article by Ed DATE FORMAT. It will clear most of your doubts regarding date format, order of precedence of the formats.

Report message to a moderator

Re: Date format in PL/SQL vs. PSP [message #613438 is a reply to message #613384]

Tue, 06 May 2014 13:48

agarfin
Messages: 3
Registered: May 2014

Junior Member

Thanks for the link, that was a good read. I ended up solving my problem by using to_char to define the date format.

Thanks for the help.

Report message to a moderator

Re: Date format in PL/SQL vs. PSP [message #613440 is a reply to message #613438]

Tue, 06 May 2014 13:57

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Say thanks to Ed for such a good write up Smile

Report message to a moderator

Previous Topic:	How to define range in where clause?
Next Topic:	ORA-00905 error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 08:33:45 CDT 2024