date & time issue 3

[ksbrace]

Hi,
I am working on a pl/sql web application. I have a form on an html page w/some fields that allow the user to update/edit the fields. I have a DATE column in the database table that contains both the date and the time. I am having difficulty updating this field. I don't seem to have the correct syntax. I can't even update it using PL/SQL Developer or SQL Plus. This is what it looks like in the database:
7/11/2002 3:40:00 PM

When I attempt to update/add/edit, I have tried:
update instances
set start_backup_time = '01-MAR-2011 3:40:00 PM'
where instance_id = 45

BUT, I am able to update just the date portion by doing:
update instances
set start_backup_time = '01-MAR-2011'
where instance_id = 45

Why do I have to alter the format when I update? i.e. the data in the database looks like: 7/11/2002 3:40:00 PM, but if I attempt to update/edit/add by typing:
update instances
set start_backup_time = '7/11/2002'
where instance_id = 45 I get an 'ORA-01843 not a valid month' Error. thanks in advance!

 

[lewisp]

Your date/time only looks like that because it has been selected from the database in that format by the form. You will find that a format mask of 'DD/MM/YYYY HH:MI:SS PM' has been applied to the field on the form.

Your database probably has a default date format of 'DD-MON-YYYY' and any non-qualified dates must be entered in that format.

To create a date in the format you require, use TO_DATE:

[tt]To_Date('7/11/2002 3:40:00 PM','DD/MM/YYYY HH:MI:SS PM')[/tt]

or

[tt]To_Date('7/11/2002','DD/MM/YYYY')[/tt]


(I assume that 7/11/2004 is 7th November rather than 11th July.)

 

[sem]

The data in the database looks like:

120,104,1,2,1,1,1

You may get it with DUMP function.

The fact that you see it in some querying tool in different format doesn't mean that it's stored the same way. Your tool (PL/SQL Deeloper) checks the time part of returned data and if it's not empty shows it. SQL plus uses default format mask (truncates time by default). When updating you should either specify format mask explicitly or change default value by

ALTER SESSION SET NLS_DATE_FORMAT=<whatever you need>

Regards, Dima

http://seminihin.narod.ru

 

[kzhangkzhang]

To see all nls related parameter for your instance, you can:


DEV> l
1 select
2 parameter
3 , value param_value
4 from nls_session_parameters
5*
DEV> /

PARAMETER PARAM_VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-YY
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

PARAMETER PARAM_VALUE
------------------------------ ----------------------------------------
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

17 rows selected.

 

[ksbrace]

Thanks guys,
One more date/time question. I was asked to add a Time field on the HTML form. I am trying to concatenate the date part of sysdate to the 24 hour time that the user enters in the form. After that, I need to convert it back to 12 hour format. Any help/guidance would be greatly appreciated. Thanks in advance!

--works fine
v_date varchar2(30) := to_char(sysdate, 'MM/DD/YYYY');

-- p_start_backup_time is passed into PL/SQL procedure
--passd in as 22:00:00
v_s_time varchar2(20):= p_start_backup_time;

-- p_end_backup_time is passed into PL/SQL procedure
--passd in as 24:00:00
v_e_time varchar2(20) := p_end_backup_time;


--I don't think these lines are correct. It doesn't generate an Oracle Error, but when I uncomment this line, I get 404 html error.  If I comment it out, I don't get 404 error, but it doesn't work(obviously) when commented out.   
v_s_all date := to_date(v_date||' '||v_s_time, 'MM/DD/YYYY HH:MI:SS PM');

v_e_all date := to_date(v_date||' '||v_e_time, 'MM/DD/YYYY HH:MI:SS PM');

if ( v_s_all <= v_e_all)  then
--something cool

 

[ksbrace]

I fixed it. If anyone cares, this is how I did it. thanks again for your help.

    v_date varchar2(30) := to_char(sysdate, 'MM/DD/YYYY');
   v_s_time varchar2(20):= p_start_backup_time;
   v_e_time varchar2(20) := p_end_backup_time;
  v_s_all varchar(30) := v_date||' '||v_s_time;
  v_e_all varchar2(30) := v_date||' '||v_e_time;

insert into Instance_table(Instance_Id, Campus_Id, Domain_Name, Ip, Os_Version, Sid, Rdbms_Version, Oem_Database_Name, Start_Backup_Time,
                             End_Backup_Time)
       Values( v_instance_id, p_campus_id, p_domain_name, p_ip_address, p_os_version, p_sid, p_rdbms_version, p_oem_db_name,
               to_date(v_s_all, 'MM/DD/YYYY HH24:MI:SS'), to_date(v_e_all, 'MM/DD/YYYY HH24:MI:SS')